[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id,
customer_id,
initial_pickup_location_id,
ultimate_dropoff_location_id,
intmed_ship_to_location_id,
planned_flag,
status_code
INTO
l_entity_table(1).p_organization_id,
l_entity_table(1).p_customer_id,
l_entity_table(1).p_ship_from_location_id,
l_entity_table(1).p_ship_to_location_id,
l_entity_table(1).p_intmed_location_id,
l_entity_table(1).p_planned_flag,
l_entity_table(1).p_status_code
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = p_delivery_id;
l_entity_table(1).p_action_code := wsh_fte_comp_constraint_grp.g_action_update;
select meaning
into l_ship_method_meaning
from fnd_lookup_values_vl
where lookup_type = 'SHIP_METHOD'
and view_application_id = 3
and lookup_code = p_ship_method_code;
SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
wnd.waybill,
GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd
WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
AND wda.delivery_id = wnd.delivery_id
and ( wdd.released_status = 'Y'
OR (wdd.released_status='X' and wdd.source_code='RTV') --RTV Change 16197273
OR ( wdd.released_status = 'X' and
exists (select 1
from mtl_system_items_b msi
where msi.organization_id = wdd.organization_id
and msi.inventory_item_id = wdd.inventory_item_id
and msi.mtl_transactions_enabled_flag = 'N')) -- for nontransactable items
)
and wdd.organization_id = p_organization_id
and wnd.name like (p_delivery_name)
AND status_code not in ('CO', 'CL', 'IT');
SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
wnd.waybill,
GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
AND wda.delivery_id = wnd.delivery_id
and wdd.organization_id = p_organization_id
and wnd.name like (p_delivery_name);
select wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
wnd.waybill,
GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
from wsh_new_deliveries_ob_grp_v wnd
where wnd.delivery_id IN -- bug 2326192
( select wda.delivery_id
from wsh_delivery_assignments_v wda
where parent_delivery_detail_id =
( select wdd.delivery_detail_id
from wsh_delivery_details_ob_grp_v wdd
where wdd.lpn_id = p_lpn_id
and wdd.organization_id = p_organization_id
)
)
and wnd.name like (p_delivery_name);
SELECT wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
wnd.waybill,
GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id IN
( SELECT wda.delivery_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.lpn_id = p_lpn_id
AND wdd.organization_id = p_organization_id
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
-- AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650 -- Commented to display the closed deliveries for bug#13990462
)
AND wnd.name like (p_delivery_name);
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries_ob_grp_v wnd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.delivery_id = p_delivery_id
AND wdd.inventory_item_id = nvl(p_inventory_item_id, wdd.inventory_item_id)
AND wdd.released_status = 'Y';
SELECT distinct PV.vendor_name, WCSM.carrier_id,
WCSM.ship_method_code
FROM WSH_CARRIER_SHIP_METHODS_V WCSM,
PO_VENDORS PV
WHERE PV.vendor_name like (p_carrier_name)
AND WCSM.carrier_id is not null
AND PV.vendor_id = WCSM.carrier_id;
select
meaning,
description,
lookup_code ship_method_code
from fnd_lookup_values_vl flv
where lookup_type = 'SHIP_METHOD'
and view_application_id = 3
and nvl(start_date_active,sysdate)<=sysdate
AND nvl(end_date_active,sysdate)>=sysdate
AND enabled_flag = 'Y'
AND meaning like ( p_ship_method_name)
AND lookup_code in (select ship_method_code
from wsh_carrier_services wcs, wsh_org_carrier_services wocs,
wsh_carriers wc
where wocs.organization_id = p_organization_id
AND wcs.ship_method_code = flv.lookup_code
AND wcs.enabled_flag = 'Y'
AND wocs.enabled_flag = 'Y'
AND wcs.carrier_service_id = wocs.carrier_service_id
and wcs.carrier_id = wc.carrier_id
AND NVL(wc.generic_flag, 'N') = 'N')
order by meaning;
SELECT wnd.name, wnd.delivery_id, nvl(wnd.gross_weight, 0), wnd.weight_uom_code,
wnd.waybill,' ',
GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = p_delivery_id;
/** ssia 10/17/2002 Add nvl(transaction_temp_id, 0) in the select statement
For serial - shipping enhancement project
**/
/*Bug#5612236. In the below queries, replaced 'MTL_SYSTEM_ITEMS_KFV' with
'MTL_SYSTEM_ITEMS_VL'.*/
x_return_Status := 'C';
SELECT ' ',del.name delivery_name, dd.delivery_detail_id,
dd.inventory_item_id,msiv.concatenated_segments, msiv.description,
dd.requested_quantity, dd.requested_quantity_uom,
dd.serial_number, del.waybill, Nvl(msiv.serial_number_control_code, 1),
dd.subinventory, Nvl(dd.locator_id,0),dd.tracking_number,
nvl(dd.transaction_temp_id,0),
--3348813
--Adding picked_quantity as part of the return cursor.
dd.picked_quantity,
dd.shipped_quantity,
--Bug 3952081
--add DUOM values
REQUESTED_QUANTITY_UOM2,
REQUESTED_QUANTITY2,
PICKED_QUANTITY2,
SHIPPED_QUANTITY2
FROM wsh_new_deliveries_ob_grp_v del, wsh_delivery_details_ob_grp_v dd,
wsh_delivery_assignments_v da, mtl_system_items_vl msiv
WHERE da.delivery_id = del.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND ( dd.inventory_item_id = p_inventory_item_id or p_inventory_item_id = -1 )
AND NVL( dd.inv_interfaced_flag, 'N') = 'N'
AND dd.released_status = 'Y'
AND del.delivery_id = p_delivery_id
AND msiv.inventory_item_id(+) = dd.inventory_item_id
AND msiv.organization_id(+) = dd.organization_id
ORDER BY dd.subinventory,dd.locator_id, msiv.concatenated_segments;
SELECT ' ',del.name delivery_name, dd.delivery_detail_id, dd.inventory_item_id,
msiv.concatenated_segments, msiv.description,
dd.requested_quantity, dd.requested_quantity_uom,
dd.serial_number, del.waybill, Nvl(msiv.serial_number_control_code, 1),
dd.subinventory, Nvl(dd.locator_id,0),dd.tracking_number,
nvl(dd.transaction_temp_id,0),
--3348813
--Adding picked_quantity as part of the return cursor.
dd.picked_quantity,
dd.shipped_quantity
FROM wsh_new_deliveries_ob_grp_v del,
wsh_delivery_details_ob_grp_v dd,
wsh_delivery_assignments_v da,
mtl_system_items_vl msiv
WHERE da.delivery_id = del.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND ( dd.inventory_item_id = p_inventory_item_id
or p_inventory_item_id = -1 )
AND NVL( dd.inv_interfaced_flag, 'N') = 'N'
AND dd.released_status = 'Y'
AND del.delivery_id = p_delivery_id
AND msiv.inventory_item_id(+) = dd.inventory_item_id
AND msiv.organization_id(+) = dd.organization_id
AND msiv.serial_number_control_code = 6
ORDER BY dd.subinventory, dd.locator_id,msiv.concatenated_segments;
select msik.concatenated_segments
from wsh_new_deliveries_ob_grp_v del,
wsh_delivery_details_ob_grp_v dd,
wsh_delivery_assignments_v da,
mtl_system_items_kfv msik
where da.delivery_id = del.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND del.delivery_id = p_delivery_id
AND msik.inventory_item_id(+) = dd.inventory_item_id
AND msik.organization_id(+) = dd.organization_id
AND msik.serial_number_control_code = 6;
select inventory_item_id, subinventory, revision, locator_id,lot_number, transaction_temp_id, serial_number,picked_quantity
into l_inventory_item_id, l_subinventory_code, l_revision, l_locator_id, l_lot_number,
l_transaction_temp_id, l_serial_number,l_picked_quantity
from wsh_delivery_details_ob_grp_v
where delivery_detail_id = p_delivery_detail_id;
select mtl_material_Transactions_s.nextval
into l_InvPCInRecType.transaction_temp_id
from dual;
debug('About to call wsh_interface.update_shipping_attributes',
'GET_DELIVERY_LINE_SERIAL_INFO');
wsh_interface.update_shipping_attributes
(x_return_status => l_return_status,
p_changed_attributes => l_detail_attributes,
p_source_code => 'INV');
debug('wsh_interface.update_shipping_attributes failed'
|| ' with status: ' || l_return_status,'GET_DELIVERY_LINE_SERIAL_INFO');
select count(*)
into l_num_serial_record
From mtl_serial_numbers_temp
where transaction_temp_id = l_transaction_temp_id;
select trip.name, trip.trip_id
into l_trip_name, l_trip_id
from wsh_trips_ob_grp_v trip,
wsh_trip_stops_ob_grp_v pickup_stop,
wsh_trip_stops_ob_grp_v dropoff_stop,
wsh_delivery_legs_ob_grp_v wdl,
wsh_new_deliveries_ob_grp_v wnd
where wdl.delivery_id = wnd.delivery_id(+)
and wdl.delivery_id = p_delivery_id
and pickup_stop.stop_id = wdl.pick_up_stop_id
and dropoff_stop.stop_id = wdl.drop_off_stop_id
and pickup_stop.trip_id = trip.trip_id(+)
and wnd.delivery_id = p_delivery_id;
select name, trip_id, ship_method_code, carrier_id
from wsh_trips_ob_grp_v
where name like p_trip_name
and status_code = 'OP';
select hrl.location_code
from wsh_trip_stops_ob_grp_v wts, hr_locations hrl
where wts.stop_location_id = hrl.location_id
and wts.trip_id = p_trip_id;
select wlpn.inventory_item_id, msik.concatenated_segments
from wms_license_plate_numbers wlpn, mtl_system_items_kfv msik
where wlpn.lpn_id = p_lpn_id
and wlpn.inventory_item_id = msik.inventory_item_id(+);
select dd.delivery_detail_id
from wsh_delivery_assignments_v da,wsh_delivery_details_ob_grp_v dd
where da.delivery_id = p_delivery_id
and da.delivery_detail_id = dd.delivery_detail_id
and dd.container_flag <>'Y';
select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
, wsh_delivery_assignments_v wda2
where wdd.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_detail_id = wdd2.delivery_detail_id
and wdd2.delivery_detail_id = p_delivery_detail_id
and wda2.delivery_detail_id = wdd.delivery_detail_id;
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = l_inner_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT wdd.lpn_id, wdd.organization_id
FROM (SELECT delivery_detail_id
FROM wsh_delivery_assignments_v wda
WHERE wda.delivery_id = p_delivery_id ) wda
, wsh_delivery_details_ob_grp_v wdd
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id IS NOT NULL;
select dd.requested_quantity, dd.picked_quantity,dd.requested_quantity2
INTO l_requested_quantity, l_picked_quantity, l_requested_quantity2
from wsh_delivery_details_ob_grp_v dd
where
dd.delivery_detail_id = l_delivery_details_id_table(l_table_index);
select lpn_id
into l_lpn_id
from wsh_delivery_details_ob_grp_v
where delivery_detail_id = l_parent_delivery_detail_id;
UPDATE WMS_LICENSE_PLATE_NUMBERS
SET lpn_context = 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
where lpn_id = l_par_lpn_id.lpn_id;
l_lpn_tbl.delete;
'wsh_delivery_details_pub.update_shipping_attributes', 'INV_DELAY_SHIPMENT');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || x_return_status, 'INV_DELAY_SHIPMENT');
select dd.delivery_detail_id, dd.requested_quantity, dd.picked_quantity
from wsh_delivery_details_ob_grp_v dd
WHERE dd.delivery_detail_id = p_delivery_detail_id;
select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
, wsh_delivery_assignments_v wda2
where wdd.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_detail_id = wdd2.delivery_detail_id
and wdd2.delivery_detail_id = p_delivery_detail_id
and wda2.delivery_detail_id = wdd.delivery_detail_id;
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = l_inner_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT lpn_id
INTO l_lpn_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE delivery_detail_id = l_parent_delivery_detail_id;
--update LPN(s) context to Resides in Inventory
FOR l_par_lpn_id IN nested_parent_lpn_cursor(l_lpn_id) LOOP
IF l_debug = 1 THEN
debug('l_par_lpn_id.lpn_id: '|| l_par_lpn_id.lpn_id, 'INV_LINE_RETURN_TO_STOCK');
UPDATE wms_license_plate_numbers
SET lpn_context = 1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id
WHERE lpn_id = l_par_lpn_id.lpn_id;
debug('Updated wms_license_plate_numbers context 1: ', 'INV_LINE_RETURN_TO_STOCK');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => p_commit_flag,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributesfailed',
'INV_LINE_RETURN_TO_STOCK');
then we want reservations to be deleted after backorder, if it is N, then we
want to retain reservations. Note that the overpicked reservations will not be
retained.
p_relieve_rsv is set by the unload page (UnloadTruckPage.java) using the value
passed in the form function parameter- RELIEVE_RSV */
IF nvl(p_relieve_rsv,'Y') = 'Y' THEN
l_bo_mode := 'UNRESERVE';
select dd.delivery_detail_id, dd.requested_quantity, dd.picked_quantity,
PICKED_QUANTITY2, REQUESTED_QUANTITY2
from wsh_delivery_details_ob_grp_v dd
WHERE dd.delivery_detail_id = p_delivery_detail_id;
select wdd.delivery_detail_id, wda.delivery_assignment_id,wda2.delivery_assignment_id
from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
, wsh_delivery_assignments_v wda2
where wdd.delivery_detail_id = wda.parent_delivery_detail_id
and wda.delivery_detail_id = wdd2.delivery_detail_id
and wdd2.delivery_detail_id = p_delivery_detail_id
and wda2.delivery_detail_id = wdd.delivery_detail_id;
SELECT lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = l_inner_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT lpn_id
INTO l_lpn_id
FROM wsh_delivery_details_ob_grp_v wdd
WHERE delivery_detail_id = l_parent_delivery_detail_id;
--update LPN(s) context to Resides in Inventory
FOR l_par_lpn_id IN nested_parent_lpn_cursor(l_lpn_id) LOOP
UPDATE wms_license_plate_numbers
SET lpn_context = 1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id
WHERE lpn_id = l_par_lpn_id.lpn_id;
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => p_commit_flag,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributesfailed',
'INV_LINE_RETURN_TO_STOCK');
debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
'INV_REPORT_MISSING_QTY');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || x_return_status, 'INV_REPORT_MISSING_QTY');
debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
'INV_REPORT_MISSING_QTY');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || x_return_status, 'INV_REPORT_MISSING_QTY');
SELECT unit_weight,
unit_volume,
nvl(wv_frozen_flag ,'N') wv_frozen_flag
FROM WSH_DELIVERY_DETAILS_OB_GRP_V
WHERE delivery_detail_id = p_delivery_line_id;
debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
'SUBMIT_DELIVERY_LINE');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || x_return_status, 'SUBMIT_DELIVERY_LINE');
SELECT unit_weight,
unit_volume,
nvl(wv_frozen_flag ,'N') wv_frozen_flag
FROM WSH_DELIVERY_DETAILS_OB_GRP_V
WHERE delivery_detail_id = p_delivery_line_id;
debug('About to call wsh_delivery_details_pub.update_shipping_attributes',
'SUBMIT_DELIVERY_LINE');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => G_TRUE,
p_commit => G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_detail_attributes,
p_source_code => 'OE');
debug('wsh_delivery_details_pub.update_shipping_attributes failed'
|| ' with status: ' || x_return_status, 'SUBMIT_DELIVERY_LINE');
SELECT source_document_type_id, source_document_id, source_document_line_id
from oe_order_lines_all
where line_id = c_order_line_id;
SELECT destination_type_code,
destination_subinventory,
source_organization_id,
destination_organization_id,
deliver_to_location_id,
pl.requisition_line_id
from po_requisition_lines_all pl,
oe_order_lines_all ol
where ol.source_document_type_id = 10
AND ol.line_id = c_order_line_id
and pl.requisition_line_id = c_po_line_id
and pl.requisition_line_id = ol.source_document_line_id
and pl.requisition_header_id = ol.source_document_id;
SELECT intransit_type
INTO l_intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = l_po_info.source_organization_id
and to_organization_id = l_po_info.destination_organization_id;
select TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID
into l_trx_act_id, l_trx_src_type
from mtl_transaction_Types
where transaction_type_id = l_trx_type_code;
SELECT source_line_id
INTO l_source_line_id
FROM wsh_delivery_details_ob_grp_v
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT dd.*
from wsh_delivery_details_ob_grp_v dd,
wsh_delivery_assignments_v da
where
da.delivery_id = p_delivery_id
and da.delivery_detail_id = dd.delivery_detail_id
and dd.lpn_id is null;
SELECT wdd1.lpn_id
from wsh_delivery_details wdd1,
wsh_delivery_details wdd2,
wsh_delivery_assignments wda
WHERE wdd2.delivery_detail_id = l_delivery_detail_id
AND wdd2.delivery_detail_id = wda.delivery_detail_id
AND wda.parent_delivery_detail_id IS NOT NULL
AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
AND wdd1.container_flag = 'Y'
AND wdd1.lpn_id is not null; --16482236
select status_control_flag
into l_status_enabled
from mtl_transaction_types
where transaction_type_id = l_trx_type_code;
SELECT wdd.ship_set_id
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND EXISTS (SELECT 'x'
FROM wsh_delivery_details_ob_grp_v wdd2
WHERE wdd2.delivery_detail_id = wdd.delivery_detail_id
AND wdd2.ship_set_id is not null
AND wdd2.shipped_quantity is not null)
AND wda.delivery_id = p_delivery_id;
SELECT count(*)
INTO unshipped_count
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries_ob_grp_v wnd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.delivery_id = p_delivery_id
AND wdd.ship_set_id = l_ship_set_id
AND wdd.shipped_quantity is null;
select set_name
into l_ship_set_name
from oe_sets
where set_id = l_ship_set_id;
select 1
into exist_unspecified
from dual
where exists (select 1
from wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.shipped_quantity is null
AND wdd.container_flag = 'N'
AND wda.delivery_id = p_delivery_id
);
select wdd.delivery_detail_id
from wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries_ob_grp_v wnd
where wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.delivery_id = p_delivery_id
AND wdd.shipped_quantity is null
AND wdd.container_flag = 'N'; --Bug 5971499
SELECT 1
INTO exist_unqualified
FROM dual
WHERE EXISTS (SELECT 1
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.container_flag = 'N'
AND (wdd.released_status NOT IN ( 'X', 'Y' )
OR (Nvl(oe_dual_uom_util.GET_FULFILLMENT_BASE(wdd.source_line_id), 'P') = 'P'
AND (wdd.cycle_count_quantity > 0
OR wdd.shipped_quantity < wdd.requested_quantity
)
)
OR (Nvl(oe_dual_uom_util.GET_FULFILLMENT_BASE(wdd.source_line_id), 'P') = 'S'
AND (wdd.cycle_count_quantity2 > 0
OR wdd.shipped_quantity2 < wdd.requested_quantity2
)
)
)
);--16739882
select 'Y'
into l_loaded
from dual
where exists (select 1
from wms_shipping_transaction_temp
where delivery_id = p_delivery_id);
SELECT s1.trip_id
FROM wsh_trip_stops s1,
wsh_delivery_legs dl1,
wsh_new_deliveries d1,
wsh_trip_stops s2,
wsh_delivery_legs dl2
WHERE d1.delivery_id <> l_delivery_id
AND s1.stop_id = dl1.pick_up_stop_id
AND d1.delivery_id = dl1.delivery_id
AND d1.status_code = 'OP'
AND d1.delivery_type = 'STANDARD'
AND s2.trip_id = s1.trip_id
AND s2.stop_id = dl2.pick_up_stop_id
AND dl2.delivery_id = l_delivery_id
AND rownum = 1;
SELECT wts.trip_id, wt.name
FROM wsh_trip_stops wts, wsh_trips wt, wsh_delivery_legs wdl
WHERE wdl.delivery_id = p_delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = wt.trip_id;
select organization_id
into l_organization_id
from wsh_new_deliveries_ob_grp_v
where delivery_id = p_delivery_id
for update NOWAIT;
SELECT count(*) INTO l_del_leg_count
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id;
SELECT 'Y'
INTO l_mul_del_trip
FROM wsh_trip_stops wts, wsh_delivery_legs wdl
WHERE wts.trip_id = l_trip_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdl.delivery_id <> p_delivery_id;
select transaction_temp_id
into l_transaction_temp_id
From wsh_delivery_details_ob_grp_v
where delivery_detail_id = l_delivery_detail_id;
select transaction_temp_id
into l_new_transaction_temp_id
from wsh_delivery_details_ob_grp_v
where delivery_detail_id = l_new_delivery_line_id;
select mtl_material_transactions_s.nextval
into l_new_transaction_temp_id
from dual;
wsh_interface.update_shipping_attributes
(x_return_status => l_return_status,
p_changed_attributes => l_detail_attributes,
p_source_code => 'INV');
debug('wsh_interface.update_shipping_attributes failed'
|| ' with status: ' || l_return_status,'INV_SPLIT_DELIVERY_LINE');
select transaction_temp_id
into l_transaction_temp_id
From wsh_delivery_details_ob_grp_v
where delivery_detail_id = l_delivery_detail_id;
select transaction_temp_id
into l_new_transaction_temp_id
from wsh_delivery_details_ob_grp_v
where delivery_detail_id = l_new_delivery_line_id;
select mtl_material_transactions_s.nextval
into l_new_transaction_temp_id
from dual;
wsh_interface.update_shipping_attributes
(x_return_status => l_return_status,
p_changed_attributes => l_detail_attributes,
p_source_code => 'INV');
debug('wsh_interface.update_shipping_attributes failed'
|| ' with status: ' || l_return_status,'INV_SPLIT_DELIVERY_LINE');
select count(*)
into l_count
from mtl_serial_numbers_temp msnt, wsh_delivery_details_ob_grp_v wdd
where msnt.transaction_temp_id = wdd.transaction_temp_id
and wdd.delivery_detail_id = l_delivery_detail_id
and wdd.transaction_temp_id = l_transaction_temp_id;
delete from mtl_serial_numbers_temp
where transaction_temp_id in (select transaction_temp_id
From wsh_delivery_details_ob_grp_v
where transaction_temp_id = l_transaction_temp_id
And delivery_detail_id = l_delivery_detail_id);
SELECT enforce_ship_method
INTO p_enforce_shipmethod
FROM wsh_global_parameters ; -- changed from wsh_shipping_parameters
SELECT wt.ship_method_code,
wt.trip_id
INTO x_trip_shipmethod_code,
p_trip_id
FROM wsh_new_deliveries del,
wsh_delivery_legs dlg,
wsh_trip_stops st,
wsh_trips wt
WHERE del.delivery_id = dlg.delivery_id
AND dlg.pick_up_stop_id = st.stop_id
AND del.initial_pickup_location_id = st.stop_location_id
AND st.trip_id = wt.trip_id
AND del.delivery_id = p_delivery_id
AND rownum = 1;
select freight_code
into l_freight_code
from wsh_carriers
where carrier_id=p_carrier_id;
SELECT lot_number INTO l_lot_number
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id
AND lot_number IS NOT NULL;
SELECT wdd.lot_number INTO l_lot_number
FROM mtl_lot_uom_class_conversions lsc
, mtl_uom_class_conversions uc
, wsh_delivery_details wdd
WHERE lsc.inventory_item_id = uc.inventory_item_id
AND uc.from_uom_code = lsc.from_uom_code
AND uc.to_uom_code = lsc.to_uom_code
AND uc.conversion_rate = lsc.conversion_rate
AND wdd.organization_id = lsc.organization_id
AND wdd.inventory_item_id = lsc.inventory_item_id
AND wdd.lot_number = lsc.lot_number
AND wdd.delivery_detail_id = p_delivery_detail_id
AND wdd.lot_number IS NOT NULL;