[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wdd.ship_set_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND EXISTS (SELECT 'x'
FROM wsh_delivery_details 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 wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries 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 wdd,
wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.shipped_quantity is null
AND wda.delivery_id = p_delivery_id
);
SELECT ' ',del.name delivery_name, dd.delivery_detail_id,
dd.inventory_item_id,msik.concatenated_segments, msik.description,
dd.requested_quantity, dd.requested_quantity_uom,
dd.serial_number, del.waybill, Nvl(msik.serial_number_control_code, 1),
dd.subinventory, Nvl(dd.locator_id,0),dd.tracking_number,
nvl(dd.transaction_temp_id,0),
dd.picked_quantity, dd.requested_quantity_uom2, NVL(dd.lot_number, ' '), NVL(dd.picked_quantity2,0)
---FROM wsh_new_deliveries_ob_grp_v del, wsh_delivery_details_ob_grp_v dd,
FROM wsh_new_deliveries del, wsh_delivery_details dd,
wsh_delivery_assignments da, mtl_system_items_kfv msik
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 msik.inventory_item_id(+) = dd.inventory_item_id
AND msik.organization_id(+) = dd.organization_id
ORDER BY dd.subinventory,dd.locator_id, msik.concatenated_segments;
select dd.delivery_detail_id,
dd.requested_quantity,
dd.picked_quantity,
NVL(dd.requested_quantity2,0),
NVL(dd.picked_quantity2, 0)
---from wsh_delivery_details_ob_grp_v dd
from wsh_delivery_details 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 wda, wsh_delivery_details_ob_grp_v wdd2
from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_delivery_details wdd2
, wsh_delivery_assignments 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
FROM wsh_delivery_details 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');
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.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');
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;
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.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');
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 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 wnd
WHERE wnd.delivery_id = p_delivery_id;
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 wnd, wsh_delivery_assignments wda,wsh_delivery_details 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')
/*
( 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')) --
)
*/
and wdd.organization_id = p_organization_id
and wnd.name like (p_delivery_name)
AND status_code not in ('CO', 'CL', 'IT');
SELECT DISTINCT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
FROM mtl_system_items_kfv msik, wsh_delivery_details dd, wsh_delivery_assignments da,
wsh_new_deliveries nd
WHERE msik.concatenated_segments LIKE (p_concatenated_segments)
AND msik.organization_id = p_organization_id
AND msik.inventory_item_id = dd.inventory_item_id
AND nd.delivery_id = p_delivery_id
AND nd.delivery_id = da.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND (dd.inv_interfaced_flag = 'N' OR dd.inv_interfaced_flag IS NULL)
AND dd.released_status = 'Y'
AND nd.status_code NOT IN ('CO', 'CL', 'IT')
--Changes for GTIN
UNION
SELECT DISTINCT msik.concatenated_segments concatenated_segments
, msik.inventory_item_id
, msik.description
FROM mtl_system_items_kfv msik,
wsh_delivery_details dd,
wsh_delivery_assignments da,
wsh_new_deliveries nd,
mtl_cross_references mcr
WHERE msik.organization_id = p_organization_id
AND msik.inventory_item_id = dd.inventory_item_id
AND nd.delivery_id = p_delivery_id
AND nd.delivery_id = da.delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND (dd.inv_interfaced_flag = 'N' OR dd.inv_interfaced_flag IS NULL)
AND dd.released_status = 'Y'
AND nd.status_code NOT IN ('CO', 'CL', 'IT')
AND msik.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = msik.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY concatenated_segments;
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;