[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wda.delivery_id
INTO l_delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details_ob_grp_v wdd,
wsh_new_deliveries_ob_grp_v wnd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.delivery_type = 'CONSOLIDATION'
AND wdd.lpn_id = p_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND ROWNUM = 1;
SELECT wnd.delivery_type
INTO l_delivery_type
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = p_delivery_id ;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT wda.delivery_id
INTO l_delivery_id
FROM mtl_material_transactions_temp mmtt,
wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda
WHERE mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id = 28
AND mmtt.transaction_source_type_id IN (2, 8)
AND mmtt.move_order_line_id = wdd.move_order_line_id
AND wdd.released_status = 'S'
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND ROWNUM =1;
SELECT wda.delivery_id
INTO l_delivery_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details_ob_grp_v wdd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.lpn_id = p_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND ROWNUM =1;
SELECT wda.delivery_id
INTO l_delivery_id
FROM mtl_txn_request_lines mtrl,
wsh_delivery_assignments_v wda
WHERE mtrl.lpn_id IN (SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id)
AND wda.delivery_detail_id = mtrl.backorder_delivery_detail_id;
SELECT 2 -- 1=AD, 2=WD
INTO l_consolidation_method_id -- 1=AD, 2=WD
FROM wms_op_plan_details wopd, mtl_material_transactions_temp mmtt
WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.transaction_action_id = 28
AND mmtt.transaction_source_type_id IN (2, 8)
AND mmtt.operation_plan_id = wopd.operation_plan_id
AND wopd.consolidation_method_id = 2 --WD
AND wopd.operation_type = 2 -- Drop
AND ROWNUM = 1;
SELECT 2 -- 1=AD, 2=WD
INTO l_consolidation_method_id -- 1=AD, 2=WD
FROM wms_op_plan_details wopd, wms_dispatched_tasks_history wdth
WHERE wdth.transfer_lpn_id = p_lpn_id
AND wdth.operation_plan_id = wopd.operation_plan_id
AND wopd.consolidation_method_id = 2 --WD
AND wopd.operation_type = 2 -- Drop
AND ROWNUM = 1;
SELECT 2
INTO l_consolidation_method_id
FROM wms_op_plan_details wopd,
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id IN (SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id)
AND mtrl.line_status = 7
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.operation_plan_id = wopd.operation_plan_id
AND wopd.consolidation_method_id = 2 --WD
AND wopd.operation_type = 2;
SELECT wlpn.lpn_id, lpn_context
FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id;
SELECT lpn_context , organization_id, outermost_lpn_id
INTO l_to_lpn_context , l_to_lpn_organization_id, l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_to_lpn_id;
SELECT 'Y'
INTO l_in_staging
FROM mtl_item_locations mil
WHERE mil.organization_id = l_to_lpn_organization_id
AND mil.subinventory_code = p_to_sub
AND mil.inventory_location_id = p_to_locator_id
AND mil.inventory_location_type = 2;
SELECT 'Y'
INTO l_in_staging
FROM mtl_item_locations mil, wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_to_lpn_id
AND wlpn.locator_id = mil.inventory_location_id
AND mil.inventory_location_type = 2;
SELECT 'Y'
INTO l_loose_exists
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_to_lpn_id
AND ROWNUM =1;
SELECT outermost_lpn_id, subinventory_code, locator_id
INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
FROM
(SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
FROM wsh_delivery_assignments wda
, wsh_delivery_details_ob_grp_v wdd
, mtl_item_locations mil
, wms_license_plate_numbers wlpn
, wms_dispatched_tasks_history wdth
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.lpn_id <> p_lpn_id
AND wlpn.outermost_lpn_id <> p_lpn_id
AND wlpn.LPN_CONTEXT = 11
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND mil.inventory_location_type = 2 -- Staging
AND wdth.transfer_lpn_id = wdd.lpn_id
AND NOT exists (SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = wlpn.outermost_lpn_id)
-- above is to check that the outermost lpn being suggested
-- does nto have Looase material
AND wda.delivery_id = l_from_delivery_id
ORDER BY wdth.creation_date DESC)
WHERE ROWNUM = 1;
SELECT outermost_lpn_id, subinventory_code, locator_id
INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
FROM
(SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
FROM wsh_delivery_assignments wda
, wsh_delivery_details_ob_grp_v wdd
, mtl_item_locations mil
, wms_license_plate_numbers wlpn
, wms_dispatched_tasks_history wdth
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.lpn_id <> p_lpn_id
AND wlpn.outermost_lpn_id <> p_lpn_id
AND wlpn.LPN_CONTEXT = 11
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND mil.inventory_location_type = 2
AND wdth.transfer_lpn_id = wdd.lpn_id
AND NOT exists (SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = wlpn.outermost_lpn_id)
AND wda.delivery_id IN (SELECT l2.delivery_id
FROM wsh_delivery_legs l1, --_ob_grp_v l1,
wsh_delivery_legs l2 --_ob_grp_v l2
WHERE l1.delivery_id = l_from_delivery_id
AND l1.parent_delivery_leg_id = l2.parent_delivery_leg_id)
--- above sub query: that this del is a prt of a consol
ORDER BY wdth.creation_date DESC)
WHERE ROWNUM = 1;
SELECT outermost_lpn_id, subinventory_code, locator_id
INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
FROM
(SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
FROM wsh_delivery_assignments wda
, wsh_delivery_details_ob_grp_v wdd
, mtl_item_locations mil
, wms_license_plate_numbers wlpn
, wms_dispatched_tasks_history wdth
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.lpn_id <> p_lpn_id
AND wlpn.outermost_lpn_id <> p_lpn_id
AND wlpn.LPN_CONTEXT = 11
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND mil.inventory_location_type = 2
AND wdth.transfer_lpn_id = wdd.lpn_id
AND NOT exists (SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = wlpn.outermost_lpn_id)
AND wda.delivery_id IN (SELECT l2.delivery_id
FROM wsh_delivery_legs_ob_grp_v l1
, wsh_delivery_legs_ob_grp_v l2
, wsh_trip_stops_ob_grp_v s
, wsh_new_deliveries_ob_grp_v d
WHERE d.delivery_id = l_from_delivery_id
AND d.initial_pickup_location_id = s.stop_location_id
AND d.delivery_id = l1.delivery_id
AND s.stop_id = l1.pick_up_stop_id
AND l1.pick_up_stop_id = l2.pick_up_stop_id)
--01/02/07:5475113 AND l1.drop_off_stop_id = l2.drop_off_stop_id)
-- above subquery: that this delvery is a part of Trip
ORDER BY wdth.creation_date DESC)
WHERE ROWNUM = 1;
SELECT 'Y'
INTO l_part_of_consol_delivery
FROM wsh_delivery_legs --_ob_grp_v
WHERE delivery_id = p_delivery_id
AND PARENT_DELIVERY_LEG_ID IS NOT NULL
AND ROWNUM = 1;