The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Shipping_Attributes(
p_source_code IN VARCHAR2
, p_changed_attributes IN ChangedAttributeTabType
, x_return_status OUT NOCOPY VARCHAR2
, p_log_level IN NUMBER -- log level fix
)
IS
l_interface_flag VARCHAR2(1);
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIPPING_ATTRIBUTES';
WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE WSH_INTERFACE.UPDATE_SHIPPING_ATTRIBUTES' );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.UPDATE_INVENTORY_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_USA_INV_PVT.Update_Inventory_Info(
p_changed_attributes => p_changed_attributes,
x_return_status => l_rs);
WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_INTERFACE.PROCESS_RECORDS / UPDATE_INVENTORY_INFO' );
END IF; -- return status after Update_INV and Process_Records
wsh_util_core.default_handler('WSH_INTERFACE.Update_Shipping_Attributes');
END Update_Shipping_Attributes;
select dd.delivery_detail_id,
s.stop_id,
s.actual_departure_date,
nd.name,
dd.shipped_quantity
from wsh_delivery_Details dd,
wsh_trip_stops s,
wsh_delivery_legs dl,
wsh_delivery_assignments_v da,
wsh_new_deliveries nd
-- wsh_delivery_line_status_v ds
where s.stop_id = dl.pick_up_stop_id
and dl.delivery_id = nd.delivery_id
and nd.delivery_id = da.delivery_id
-- and dd.delivery_detail_id = ds.delivery_detail_id
and da.delivery_detail_id = dd.delivery_detail_id
and s.stop_location_id = nd.initial_pickup_location_id
and dd.customer_id = p_customer_id
and dd.ship_to_location_id = l_ship_to_location_id
and dd.ship_from_location_id = l_ship_from_location_id
and dd.inventory_item_id = p_inventory_item_id
and dd.source_header_id = p_order_header_id
-- and ds.delivery_status in ('CL', 'IT','CO')
and NVL(nd.shipment_direction, 'O') IN ('O', 'IO')
and NVL(dd.customer_prod_seq,'*') = NVL(NVL(p_cust_production_seq_num,dd.customer_prod_seq), '*');
SELECT mci.master_container_item_id, mci.detail_container_item_id
FROM mtl_customer_items mci, wsh_delivery_details dd, oe_order_lines_all ool
WHERE dd.delivery_detail_id = p_delivery_detail_id AND
dd.source_line_id = ool.line_id AND
mci.customer_item_id(+) = ool.ordered_item_id AND
ool.item_identifier_type = 'CUST';
UPDATE wsh_delivery_details
SET master_container_item_id = l_master_container_item_id,
detail_container_item_id = l_detail_container_item_id
WHERE delivery_detail_id = p_delivery_detail_id;
p_cancel_delete_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_assignment(c_detail_id NUMBER) IS
SELECT delivery_assignment_id,
parent_delivery_detail_id,
delivery_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = c_detail_id;
SELECT delivery_detail_id,
organization_id,
ship_from_location_id,
inventory_item_id,
requested_quantity,
picked_quantity,
requested_quantity2,
picked_quantity2,
move_order_line_id,
released_status,
source_code,
container_flag,
source_line_id -- Column added for Bug 5741373
FROM wsh_delivery_details
WHERE delivery_detail_id = c_detail_id;
WSH_DELETE_DETAIL_FAILED EXCEPTION;
WSH_DEBUG_SV.log(l_module_name,'p_cancel_delete_flag',p_cancel_delete_flag);
GOTO loop_end; -- maybe already deleted.
SELECT container_name
INTO l_container_name
FROM wsh_delivery_details
WHERE delivery_detail_id = l_assign_rec.parent_delivery_detail_id;
SELECT planned_flag
INTO l_planned_flag
FROM wsh_new_deliveries
WHERE delivery_id = l_assign_rec.delivery_id;
l_trolin_tbl(l_trolin_table_id).OPERATION := INV_GLOBALS.G_OPR_DELETE;
WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE MOVE ORDER LINE '||L_TROLIN_REC.LINE_ID );
/* H integration: 940/945 cancel 'WSH' line, not delete it wrudge */
IF l_detail_rec.source_code = 'WSH' AND
l_detail_rec.container_flag = 'N' THEN
l_cancel_dds( l_cancel_dds.count+1 ) := p_details_id(i);
IF (p_cancel_delete_flag = 'C') THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS to CANCEL',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
p_delivery_detail_id => p_details_id(i),
p_cancel_flag => 'Y',
x_return_status => l_return_status );
ELSE -- delete details
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS to DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details(
p_delivery_detail_id => p_details_id(i),
p_cancel_flag => 'N',
x_return_status => l_return_status );
WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE DELIVERY DETAIL '|| P_DETAILS_ID ( I ) || ' FAILED' );
raise WSH_DELETE_DETAIL_FAILED;
p_delete_reservations => 'Y',
p_txn_source_line_id => l_source_line_ids(i) );
UPDATE wsh_delivery_details
SET move_order_line_id = NULL ,
released_status = 'D',
cycle_count_quantity = NULL,
cycle_count_quantity2 = NULL,
shipped_quantity = NULL,
shipped_quantity2 = NULL,
picked_quantity = NULL,
picked_quantity2 = NULL,
subinventory = NULL,
inv_interfaced_flag = NULL,
oe_interfaced_flag = NULL,
locator_id = NULL,
preferred_grade = NULL,
-- HW OPMCONV - No need for sublot_number
-- sublot_number = NULL,
lot_number = NULL,
revision = null ,
tracking_number = NULL
WHERE delivery_detail_id = l_cancel_dds(i);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_cancel_dds,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
WHEN WSH_DELETE_DETAIL_FAILED THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELETE_DETAIL_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELETE_DETAIL_FAILED');
PROCEDURE Delete_Details(
p_details_id 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_PKG_NAME || '.' || 'DELETE_DETAILS';
p_cancel_delete_flag => 'D',
x_return_status => x_return_status);
wsh_util_core.default_handler('WSH_INTERFACE.DELETE_DETAILS');
END Delete_Details;
p_cancel_delete_flag => 'C',
x_return_status => x_return_status);
select detail_container_item_id , inventory_item_id , organization_id
from wsh_delivery_Details
where move_order_line_id = p_move_order_line_id
and nvl(line_direction, 'O') IN ('O', 'IO')
and rownum = 1 ;
select max_load_quantity
into l_max_load_quantity
from wsh_container_items
where container_item_id = l_detail_rec.detail_container_item_id
and nvl ( load_item_id , l_detail_rec.inventory_item_id ) = l_detail_rec.inventory_item_id
and master_organization_id = l_detail_rec.organization_id
and rownum = 1 ;
select max_load_quantity , container_item_id
into l_max_load_quantity , l_container_item_id
from wsh_container_items
where load_item_id = l_detail_rec.inventory_item_id
and preferred_flag ='Y'
and master_organization_id = l_detail_rec.organization_id
and rownum = 1 ;
SELECT wdd.delivery_detail_id,wdd.client_id -- LSP PROJECT : Added clientId
FROM wsh_delivery_details wdd
WHERE wdd.source_line_id IN (x_source_line_id, x_neg_source_line_id)
AND wdd.source_code = x_source_code
AND wdd.container_flag = 'N'
AND wdd.released_status <> 'D';
SELECT wdd.client_id -- LSP PROJECT : Added clientId
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = c_dd_id;
FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_ATTR_NOT_ALLOWED');
FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_ATTR_NOT_ALLOWED');
select ship_set_id
from wsh_delivery_details
where source_header_id = c_source_header_id
and ship_set_id = c_ship_set
and source_code = p_source_code
and oe_interfaced_flag = 'P'
and rownum = 1;
SELECT wdd.oe_interfaced_flag,
wdd.released_status,
wdd.ship_set_id,
wdd.source_header_id,
wda.delivery_id
INTO l_interfaced_flag,
l_det_status_code,
l_ship_set_id,
l_source_header_id,
l_dummy_id
FROM wsh_delivery_details wdd,
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.container_flag = 'N'
FOR UPDATE NOWAIT;
SELECT wnd.status_code
INTO l_del_status_code
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = l_dummy_id;
SELECT wnd.status_code
INTO l_del_status_code
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = l_dummy_id
FOR UPDATE NOWAIT;
l_update_allowed VARCHAR2(1);
** During OM Interface, allow updates/splits to happen.
** Otherwise, check if changes are allowed.
*/
IF p_interface_flag <> 'Y' THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_DELIVERY_UTIL.CHECK_UPDATES_ALLOWED' );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_UTIL.CHECK_UPDATES_ALLOWED',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DELIVERY_UTIL.Check_Updates_Allowed(
p_changed_attributes => p_changed_attributes,
p_source_code => p_source_code,
x_update_allowed => l_update_allowed,
x_return_status => l_rs);
WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING CHECK_UPDATES_ALLOWED ' || L_RS );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Check_Updates_Allowed',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DELIVERY_DETAILS_UTILITIES.Check_Updates_Allowed(
p_changed_attributes => p_changed_attributes,
p_source_code => p_source_code,
x_update_allowed => l_update_allowed,
x_return_status => l_rs);
WSH_DEBUG_SV.log(l_module_name, 'After Calling WSH_DELIVERY_DETAILS_UTILITIES.Check_Updates_Allowed', l_rs);
WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_ACTIONS_PVT.UPDATE_RECORDS' );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_ACTIONS_PVT.UPDATE_RECORDS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_USA_ACTIONS_PVT.Update_Records(
p_source_code => p_source_code,
p_changed_attributes => p_changed_attributes,
p_interface_flag => p_interface_flag,
x_return_status => l_rs);
WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY' );
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 =>p_changed_attributes(l_counter),
p_source_code =>p_source_code,
p_action_flag => 'D',
x_return_status => l_rs);
SELECT wdd.delivery_detail_id,
wdd.requested_quantity,
wdd.shipped_quantity,
wdd.picked_quantity,
wdd.gross_weight,
wdd.net_weight,
wdd.weight_uom_code,
wdd.volume,
wdd.volume_uom_code,
wda.delivery_id,
wda.parent_delivery_detail_id,
wdd.released_status
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = v_delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id;
SELECT wdd.delivery_detail_id,
wdd.requested_quantity,
wdd.shipped_quantity,
wdd.picked_quantity,
wdd.gross_weight,
wdd.net_weight,
wdd.weight_uom_code,
wdd.volume,
wdd.volume_uom_code,
wda.delivery_id,
wda.parent_delivery_detail_id,
wdd.released_status
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.source_line_id = v_source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.line_direction, 'O') IN ('O', 'IO');
l_dd_tab.delete;