The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_error_tbl.delete;
SELECT ps.location_id,ps.party_site_id,ps.party_id,ps.status -- IB-Phase-2 Vendor Merge
FROM hz_party_sites ps,hz_party_site_uses psu
WHERE ps.party_site_id = psu.party_site_id
AND psu.site_use_type = 'SUPPLIER_SHIP_FROM'
and party_site_number=cp_location_code
and party_id =cp_party_id;
WSH_SUPPLIER_PARTY.Update_address(
P_location_id => x_location_id,
P_party_id => p_party_id,
P_party_site_id => l_party_site_id,
P_address1 => P_address1,
P_address2 => P_address2,
P_address3 => P_address3,
P_address4 => P_address4,
P_city => P_city,
P_postal_code => P_postal_code,
P_state => P_state,
P_province => P_province,
P_county => P_county,
p_country => p_country,
p_shipper_name => p_shipper_name,
p_phone => p_phone,
p_email => p_email,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_Address l_return_status',l_return_status);
WSH_BULK_PROCESS_PVT.bulk_insert_details (
P_line_rec => l_line_rec,
p_index => l_index,
p_action_prms => l_action_prms,
p_additional_line_info_rec => l_additional_line_info_rec,
X_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'WSH_BULK_PROCESS_PVT.bulk_insert_details
l_return_status',l_return_status);
p_msg_data => 'WSH_BULK_INSERT_FAILED');
SELECT 1
FROM mtl_units_of_measure
WHERE uom_code = p_uom
AND uom_class = 'Quantity';
PROCEDURE Update_Split_Details(
p_detail_ids IN wsh_util_core.id_tab_type,
p_detail_att IN detail_att_rec_type,
x_new_detail_ids OUT NOCOPY 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 || '.' || 'Update_Split_Details';
UPDATE wsh_delivery_details
SET requested_quantity = p_detail_att.requested_quantity,
requested_quantity2 = decode(p_detail_att.requested_quantity2,0,NULL,p_detail_att.requested_quantity2),
picked_quantity = l_picked_quantity,
picked_quantity2 = l_picked_quantity2,
routing_req_id = p_detail_att.routing_req_id,
earliest_pickup_date = l_earliest_pickup_date,
latest_pickup_date = l_latest_pickup_date,
party_id = p_detail_att.party_id,
ship_from_location_id = p_detail_att.ship_from_location_id,
ignore_for_planning = 'N',
wv_frozen_flag = p_detail_att.dd_wv_frozen_flag,
net_weight = p_detail_att.dd_net_weight,
gross_weight = p_detail_att.dd_gross_weight,
volume = p_detail_att.dd_volume,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_detail_id = l_first_detail_id
AND released_status='X';
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => x_new_detail_ids,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
UPDATE wsh_delivery_details
SET ship_from_location_id = -1,
ignore_for_planning = 'Y',
routing_req_id = null,
picked_quantity = null,
picked_quantity2 = null,
earliest_pickup_date = null,
latest_pickup_date = null,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_detail_id = l_split_delivery_detail_id;
UPDATE wsh_delivery_details
SET net_weight = nvl(p_detail_att.weight,net_weight),
gross_weight = nvl(p_detail_att.weight,gross_weight),
weight_uom_code = nvl(p_detail_att.weight_uom,weight_uom_code),
volume = nvl(p_detail_att.volume,volume),
volume_uom_code = nvl(p_detail_att.volume_uom,volume_uom_code),
wv_frozen_flag= decode(l_frozen_flag,'N',wv_frozen_flag,'Y',l_frozen_flag),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_detail_id = l_first_detail_id
AND released_status='X';
WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details (
p_delivery_detail_id => p_detail_ids(l_index),
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Delete_Delivery_Details l_return_status',l_return_status);
END Update_Split_Details;
SELECT wdd.delivery_detail_id,wdd.released_status,wda.delivery_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE routing_req_id = p_routing_req_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
ORDER BY wda.delivery_id;
SELECT net_weight, gross_weight, weight_uom_code
FROM wsh_new_deliveries
WHERE delivery_id = p_deliveryid;
SELECT wdd.source_header_number,
wdd.source_line_number,
wdd.po_shipment_line_number,
wdd.source_blanket_reference_num,
wda.delivery_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = p_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id;
UPDATE wsh_new_deliveries
SET number_of_lpn = p_delivery.number_of_containers(p_delivery_index),
net_weight = nvl(p_delivery.total_weight(p_delivery_index),net_weight),
gross_weight = nvl(p_delivery.total_weight(p_delivery_index),gross_weight),
weight_uom_code = nvl(p_delivery.weight_uom(p_delivery_index),weight_uom_code),
volume = nvl(p_delivery.total_volume(p_delivery_index),volume),
volume_uom_code = nvl(p_delivery.volume_uom(p_delivery_index),volume_uom_code),
wv_frozen_flag = l_frozen,
additional_shipment_info = p_delivery.remark(p_delivery_index),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_id = x_new_deliveries(i);
p_update_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_return_status => l_return_status);
SELECT revision_number,transaction_id
FROM wsh_inbound_txn_history
WHERE receipt_number = cp_request_number
AND TRANSACTION_TYPE ='ROUTING_REQUEST'
AND SUPPLIER_ID=p_supplier_id
ORDER BY revision_number desc;
SELECT organization_id
FROM HR_ORGANIZATION_UNITS
WHERE name = p_org_name;
SELECT delivery_detail_id,
organization_id,
source_header_id,
source_line_id,
po_shipment_line_id,
source_blanket_reference_id,
inventory_item_id,
weight_uom_code,
volume_uom_code,
routing_req_id,
requested_quantity,
nvl(requested_quantity2,0) requested_quantity2,
requested_quantity_uom,
requested_quantity_uom2,
org_id,
source_code,
released_status,
source_header_type_id,
source_header_type_name,
date_requested,
earliest_dropoff_date,
latest_dropoff_date,
shipping_control
FROM wsh_delivery_details
WHERE org_id=p_org_id
AND source_header_number = p_po_header_number
AND source_line_number = p_po_line_number
AND po_shipment_line_number = p_po_shipment_number
AND nvl(SOURCE_BLANKET_REFERENCE_NUM,-1) =nvl(nvl(p_po_release_number, SOURCE_BLANKET_REFERENCE_NUM),-1)
AND nvl(line_direction,'O') not in ('O','IO')
AND vendor_id = p_vendor_id
AND source_code='PO';
SELECT delivery_detail_id,
source_header_id,
source_line_id,
organization_id,
inventory_item_id,
weight_uom_code,
volume_uom_code,
routing_req_id,
requested_quantity,
requested_quantity_uom,
nvl(requested_quantity2,0) requested_quantity2,
requested_quantity_uom2,
po_shipment_line_id,
source_blanket_reference_id,
nvl(net_weight,0) net_weight,
nvl(gross_weight,0) gross_weight,
nvl(volume,0) volume,
wv_frozen_flag
FROM wsh_delivery_details
WHERE org_id=p_org_id
AND source_header_number = p_po_header_number
AND source_line_number = p_po_line_number
AND po_shipment_line_number = p_po_shipment_number
AND nvl(SOURCE_BLANKET_REFERENCE_NUM,-1) =nvl(nvl(p_po_release_number, SOURCE_BLANKET_REFERENCE_NUM),-1)
AND nvl(line_direction,'O') not in ('O','IO')
AND vendor_id = p_vendor_id
AND routing_req_id IS NULL
AND shipping_control = 'BUYER'
AND released_status = 'X'
AND source_code='PO'
FOR UPDATE NOWAIT ; --halock
l_new_detail_ids.delete;
l_sum_new_detail_ids.delete;
l_new_deliveries.delete;
l_detail_ids.delete;
WSH_DEBUG_SV.log(l_module_name,'Before updates');
WSH_DEBUG_SV.log(l_module_name,'After updates');
Update_Split_Details(
p_detail_att => l_detail_att,
p_detail_ids => l_detail_ids,
x_new_detail_ids => l_new_detail_ids,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_Split_Details l_return_status',l_return_status);
l_new_detail_ids.delete;
l_sum_new_detail_ids.delete;
l_new_deliveries.delete;
g_error_tbl.delete;
x_message_tbl.delete;
g_error_tbl.delete;