[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_num_empty_locators.DELETE;
SELECT status_code into l_material_status
FROM mtl_material_statuses_vl
WHERE status_id= p_status_id ;
SELECT application_short_name
FROM fnd_application
WHERE application_id = p_application_id;
SELECT NVL(SUM(onhand.oh_quantity), 0)
FROM (-- on-hand
SELECT moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity oh_quantity
FROM mtl_onhand_quantities_detail moq
-- to be more conservative ( or simply realistic ) we don't add
-- negative on-hand to the capacity
WHERE moq.transaction_quantity > 0
UNION ALL
-- pending issues/receipts and issues in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, DECODE(
mmtt.transaction_action_id
, 2, -ABS(mmtt.primary_quantity)
, 3, -ABS(mmtt.primary_quantity)
, mmtt.primary_quantity
)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(transaction_status, -1) <> 2 -- not suggestions
UNION ALL
-- receiving side in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
AND mmtt.transaction_action_id IN (2, 3) -- transfers
UNION ALL
-- note: we don't add pick suggestions to capacity
--
-- put away suggestions (including transfers)
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, DECODE(
mmtt.transaction_action_id
, 2, mmtt.transfer_subinventory
, 3, mmtt.transfer_subinventory
, mmtt.subinventory_code
)
, DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
, ABS(mmtt.primary_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.posting_flag = 'Y'
AND mmtt.transaction_status = 2 -- suggestions
AND mmtt.transaction_action_id IN -- put away
(2, 3, 12, 27, 31, 33) -- only receipts and transfer
UNION ALL
-- put away suggestions still sitting in internal temp table
SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
, mtrl.inventory_item_id
, wtt.to_subinventory_code subinventory_code
, wtt.to_locator_id locator_id
, wtt.primary_quantity
FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
WHERE wtt.type_code = 1 -- put away
AND wtt.line_type_code = 2 -- output
AND mtrl.line_id = wtt.transaction_temp_id
AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
WHERE onhand.organization_id = p_organization_id
AND onhand.inventory_item_id = p_inventory_item_id
GROUP BY onhand.inventory_item_id;
SELECT NVL(SUM(onhand.oh_quantity), 0)
FROM (-- on-hand
SELECT moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity oh_quantity
FROM mtl_onhand_quantities_detail moq
-- to be more conservative ( or simply realistic ) we don't add
-- negative on-hand to the capacity
WHERE moq.transaction_quantity > 0
UNION ALL
-- pending issues/receipts and issues in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, DECODE(
mmtt.transaction_action_id
, 2, -ABS(mmtt.primary_quantity)
, 3, -ABS(mmtt.primary_quantity)
, mmtt.primary_quantity
)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(transaction_status, -1) <> 2 -- not suggestions
UNION ALL
-- receiving side in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
AND mmtt.transaction_action_id IN (2, 3) -- transfers
UNION ALL
-- note: we don't add pick suggestions to capacity
--
-- put away suggestions (including transfers)
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, DECODE(
mmtt.transaction_action_id
, 2, mmtt.transfer_subinventory
, 3, mmtt.transfer_subinventory
, mmtt.subinventory_code
)
, DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
, ABS(mmtt.primary_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.posting_flag = 'Y'
AND mmtt.transaction_status = 2 -- suggestions
AND mmtt.transaction_action_id IN -- put away
(2, 3, 12, 27, 31, 33) -- only receipts and transfer
UNION ALL
-- put away suggestions still sitting in internal temp table
SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
, mtrl.inventory_item_id
, wtt.to_subinventory_code subinventory_code
, wtt.to_locator_id locator_id
, wtt.primary_quantity
FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
WHERE wtt.type_code = 1 -- put away
AND wtt.line_type_code = 2 -- output
AND mtrl.line_id = wtt.transaction_temp_id
AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
WHERE onhand.organization_id = p_organization_id
AND onhand.inventory_item_id = p_inventory_item_id
AND onhand.subinventory_code = p_subinventory_code
GROUP BY onhand.inventory_item_id;
SELECT onhand.locator_id, NVL(SUM(onhand.oh_quantity), 0)
FROM (-- on-hand
SELECT moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity oh_quantity
FROM mtl_onhand_quantities_detail moq
-- to be more conservative ( or simply realistic ) we don't add
-- negative on-hand to the capacity
WHERE moq.transaction_quantity > 0
UNION ALL
-- pending issues/receipts and issues in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, DECODE(
mmtt.transaction_action_id
, 2, -ABS(mmtt.primary_quantity)
, 3, -ABS(mmtt.primary_quantity)
, mmtt.primary_quantity
)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(transaction_status, -1) <> 2 -- not suggestions
UNION ALL
-- receiving side in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
AND mmtt.transaction_action_id IN (2, 3) -- transfers
UNION ALL
-- note: we don't add pick suggestions to capacity
--
-- put away suggestions (including transfers)
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, DECODE(
mmtt.transaction_action_id
, 2, mmtt.transfer_subinventory
, 3, mmtt.transfer_subinventory
, mmtt.subinventory_code
)
, DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
, ABS(mmtt.primary_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.posting_flag = 'Y'
AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
AND mmtt.transaction_status = 2 -- suggestions
AND mmtt.transaction_action_id IN -- put away
(2, 3, 12, 27, 31, 33) -- only receipts and transfer
UNION ALL
-- put away suggestions still sitting in internal temp table
SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
, mtrl.inventory_item_id
, wtt.to_subinventory_code subinventory_code
, wtt.to_locator_id locator_id
, wtt.primary_quantity
FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
WHERE wtt.type_code = 1 -- put away
AND wtt.line_type_code = 2 -- output
AND mtrl.line_id = wtt.transaction_temp_id
AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
WHERE onhand.organization_id = p_organization_id
AND onhand.inventory_item_id = p_inventory_item_id
--AND onhand.subinventory_code = p_subinventory_code
--AND onhand.locator_id = p_locator_id
GROUP BY onhand.locator_id, onhand.inventory_item_id;
g_locator_item_quantity.DELETE; -- Re-initialize the tables before re-using it
g_bulkCollect_Locator.DELETE;
g_bulkCollect_quantity.DELETE;
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_locator_inventory_item_id;
SELECT onhand.inventory_item_id
, NVL(SUM(onhand.oh_quantity), 0)
, msi.primary_uom_code
FROM mtl_system_items msi
, (-- on-hand
SELECT moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity oh_quantity
FROM mtl_onhand_quantities_detail moq
-- to be more conservative ( or simply realistic ) we don't add
-- negative on-hand to the capacity
WHERE moq.transaction_quantity > 0
UNION ALL
-- pending issues/receipts and issues in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, DECODE(
mmtt.transaction_action_id
, 2, -ABS(mmtt.primary_quantity)
, 3, -ABS(mmtt.primary_quantity)
, mmtt.primary_quantity
)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(transaction_status, -1) <> 2 -- not suggestions
UNION ALL
-- receiving side in transfers
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id > 0 -- Index !!!
AND mmtt.posting_flag = 'Y' -- pending txn
AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
AND mmtt.transaction_action_id IN (2, 3) -- transfers
UNION ALL
-- note: we don't add pick suggestions to capacity
--
-- put away suggestions (including transfers)
SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
, mmtt.inventory_item_id
, DECODE(
mmtt.transaction_action_id
, 2, mmtt.transfer_subinventory
, 3, mmtt.transfer_subinventory
, mmtt.subinventory_code
)
, DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
, ABS(mmtt.primary_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_status = 2 -- suggestions
-- AND mmtt.posting_flag = 'Y' /* 3446963 */
AND mmtt.transaction_action_id IN -- put away
(2, 3, 12, 27, 31, 33) -- only receipts and transfer
UNION ALL
-- put away suggestions still sitting in internal temp table
SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
, mtrl.inventory_item_id
, wtt.to_subinventory_code subinventory_code
, wtt.to_locator_id locator_id
, wtt.primary_quantity
FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
WHERE wtt.type_code = 1 -- put away
AND wtt.line_type_code = 2 -- output
AND mtrl.line_id = wtt.transaction_temp_id
AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
WHERE onhand.organization_id = p_organization_id
AND onhand.subinventory_code = p_subinventory_code
AND onhand.locator_id = p_locator_id
AND msi.inventory_item_id = onhand.inventory_item_id
AND msi.organization_id = p_organization_id
GROUP BY onhand.inventory_item_id, msi.primary_uom_code;
SELECT 'Y'
INTO l_return_value
FROM DUAL
WHERE EXISTS( SELECT 'Y'
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id);
SELECT SUM(moqdx.primary_transaction_quantity)
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
WHERE wlpn1.outermost_lpn_id = p_lpn_id)
AND moqdx.inventory_item_id = p_inventory_item_id
AND NVL(moqdx.revision, '-99') = NVL(p_revision, '-99')
AND NVL(moqdx.lot_number, '-9999') = NVL(p_lot_number, '-9999');
SELECT SUM(moqdx.primary_transaction_quantity)
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
WHERE wlpn1.outermost_lpn_id = p_lpn_id)
AND moqdx.inventory_item_id = p_inventory_item_id;
SELECT COUNT(DISTINCT (moqdx.inventory_item_id)) - 1
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
WHERE wlpn1.outermost_lpn_id = p_lpn_id);
SELECT COUNT(DISTINCT (moqdx.revision)) - 1
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
WHERE wlpn1.outermost_lpn_id = p_lpn_id)
AND moqdx.inventory_item_id = p_inventory_item_id;
SELECT COUNT(DISTINCT (moqdx.lot_number)) - 1
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
WHERE wlpn1.outermost_lpn_id = p_lpn_id)
AND moqdx.inventory_item_id = p_inventory_item_id;
SELECT SUM(moqdx.primary_transaction_quantity)
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id = p_lpn_id
AND moqdx.inventory_item_id = p_inventory_item_id
AND NVL(moqdx.revision, '-99') = NVL(p_revision, '-99')
AND NVL(moqdx.lot_number, '-9999') = NVL(p_lot_number, '-9999');
SELECT SUM(moqdx.primary_transaction_quantity)
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id = p_lpn_id
AND moqdx.inventory_item_id = p_inventory_item_id;
SELECT COUNT(DISTINCT (moqdx.inventory_item_id))
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id = p_lpn_id;
SELECT COUNT(DISTINCT (moqdx.revision))
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id = p_lpn_id
AND moqdx.inventory_item_id = p_inventory_item_id;
SELECT COUNT(DISTINCT (moqdx.lot_number))
INTO l_return_value
FROM mtl_onhand_quantities_detail moqdx
WHERE moqdx.lpn_id = p_lpn_id
AND moqdx.inventory_item_id = p_inventory_item_id;
SELECT COUNT(wlpnx.lpn_id)
INTO l_return_value
FROM wms_license_plate_numbers wlpnx
START WITH wlpnx.lpn_id = p_lpn_id
CONNECT BY wlpnx.lpn_id = PRIOR parent_lpn_id;
SELECT po_header_id
, po_line_id
FROM po_distributions_all
WHERE po_distribution_id = p_reference_id;
SELECT po_header_id
, po_line_id
FROM po_line_locations_all
WHERE line_location_id = p_reference_id;
SELECT po_header_id
, po_line_id
FROM rcv_transactions
WHERE transaction_id = p_reference_id;
SELECT MIN(ABS(NVL(milx.picking_order, -9999) - NVL(mil.picking_order, 9999)))
FROM mtl_item_locations mil, mtl_item_locations milx, mtl_onhand_quantities_detail moq
WHERE mil.inventory_location_id = p_locator_id
AND mil.organization_id = p_organization_id
AND moq.organization_id = p_organization_id
AND moq.inventory_item_id = p_inventory_item_id
AND moq.subinventory_code = p_subinventory_code
AND milx.organization_id = moq.organization_id
AND milx.inventory_location_id = moq.locator_id;
SELECT MIN(
((NVL(milx.x_coordinate, -9999) - NVL(mil.x_coordinate, 9999)) * (NVL(milx.x_coordinate, -9999) - NVL(
mil.x_coordinate
, 9999
)
)
)
+ ((NVL(milx.y_coordinate, -9999) - NVL(mil.y_coordinate, 9999)) * (NVL(milx.y_coordinate, -9999) - NVL(
mil.y_coordinate
, 9999
)
)
)
+ ((NVL(milx.z_coordinate, -9999) - NVL(mil.z_coordinate, 9999)) * (NVL(milx.z_coordinate, -9999) - NVL(
mil.z_coordinate
, 9999
)
)
)
)
FROM mtl_item_locations mil, mtl_item_locations milx, mtl_onhand_quantities_detail moq
WHERE mil.inventory_location_id = p_locator_id
AND mil.organization_id = p_organization_id
AND moq.organization_id = p_organization_id
AND moq.inventory_item_id = p_inventory_item_id
AND moq.subinventory_code = p_subinventory_code
AND milx.organization_id = moq.organization_id
AND milx.inventory_location_id = moq.locator_id;
SELECT COUNT(inventory_item_id)
FROM (SELECT inventory_item_id
FROM (--current onhand
SELECT inventory_item_id
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND inventory_item_id <> p_inventory_item_id
UNION ALL
--pending receipts and putaway suggestions
SELECT inventory_item_id
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND inventory_item_id <> p_inventory_item_id
AND transaction_action_id IN (12, 27, 31)
UNION ALL
--pending transfers and suggestions
SELECT inventory_item_id
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND transfer_subinventory = p_subinventory_code
AND transfer_to_location = p_locator_id
AND inventory_item_id <> p_inventory_item_id
AND transaction_action_id IN (2, 3, 28))
GROUP BY inventory_item_id);
SELECT COUNT(lot_number)
FROM (SELECT lot_number
FROM (--current onhand
SELECT lot_number
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND lot_number IS NOT NULL
AND lot_number <> p_lot_number
UNION ALL
--pending receipts and putaway suggestions (lot in MMTT)
SELECT lot_number
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND lot_number IS NOT NULL
AND lot_number <> p_lot_number
AND transaction_action_id IN (12, 27, 31)
UNION ALL
--pending transfers and suggestions (lot in MMTT)
SELECT lot_number
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND transfer_subinventory = p_subinventory_code
AND transfer_to_location = p_locator_id
AND lot_number IS NOT NULL
AND lot_number <> p_lot_number
AND transaction_action_id IN (2, 3, 28)
UNION ALL
--pending receipts and putaway suggestions (lot in MTLT)
SELECT mtlt.lot_number
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_locator_id
AND mmtt.lot_number IS NULL
AND mmtt.transaction_action_id IN (12, 27, 31)
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number <> p_lot_number
UNION ALL
--pending transfers and suggestions (lot in MTLT)
SELECT mtlt.lot_number
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.transfer_subinventory = p_subinventory_code
AND mmtt.transfer_to_location = p_locator_id
AND mmtt.lot_number IS NULL
AND mmtt.transaction_action_id IN (2, 3, 28)
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number <> p_lot_number
UNION ALL
--putaway suggestions already created for this move order
SELECT lot_number
FROM wms_transactions_temp
WHERE type_code = 1
AND line_type_code = 2
AND to_subinventory_code = p_subinventory_code
AND to_locator_id = p_locator_id
AND lot_number IS NOT NULL
AND lot_number <> p_lot_number)
GROUP BY lot_number);
SELECT COUNT(revision)
FROM (SELECT revision
FROM (--current onhand
SELECT revision
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND inventory_item_id = p_inventory_item_id
AND revision IS NOT NULL
AND revision <> p_revision
UNION ALL
--pending receipts and putaway suggestions
SELECT revision
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND inventory_item_id = p_inventory_item_id
AND revision IS NOT NULL
AND revision <> p_revision
AND transaction_action_id IN (12, 27, 31)
UNION ALL
--pending transfer txns and suggestions
SELECT revision
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND transfer_subinventory = p_subinventory_code
AND transfer_to_location = p_locator_id
AND inventory_item_id = p_inventory_item_id
AND revision IS NOT NULL
AND revision <> p_revision
AND transaction_action_id IN (2, 3, 28)
UNION ALL
--suggestions already created for this move order line
SELECT revision
FROM wms_transactions_temp
WHERE type_code = 1
AND line_type_code = 2
AND to_subinventory_code = p_subinventory_code
AND to_locator_id = p_locator_id
AND revision IS NOT NULL
AND revision <> p_revision)
GROUP BY revision);
SELECT COUNT(inventory_location_id)
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND empty_flag = 'Y';
SELECT header_id
, line_id
FROM oe_order_lines_all
WHERE line_id = p_reference_id;
SELECT source_header_id
, source_line_id
FROM wsh_delivery_details
WHERE move_order_line_id = p_line_id;
SELECT oe_order_header_id
, oe_order_line_id
FROM rcv_transactions
WHERE transaction_id = p_reference_id
AND routing_header_id = 3 ;
SELECT wda.delivery_id
INTO l_delivery_id
FROM
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE
mmtt.cartonization_id = p_lpn_id
AND mmtt.move_order_line_id = wdd.move_order_line_id -- kkoothan Removed the NVL as part of Bug Fix:2631051
AND wdd.delivery_detail_id = wda.delivery_detail_id
GROUP BY wda.delivery_id;
SELECT 'Y'
INTO multiple_lpns
FROM DUAL
WHERE EXISTS( SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE NVL(mmtt.cartonization_id, -1) <> p_lpn_id
AND mmtt.move_order_line_id = wdd.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = l_delivery_id);
SELECT MIN(DECODE(orig_date_received, NULL, date_received, LEAST(date_received, orig_date_received)))
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND subinventory_code = p_sub
AND NVL(locator_id, -1) = NVL(p_loc_id, NVL(locator_id, -1))
AND NVL(revision, '-1') = NVL(p_rev, NVL(revision, '-1'))
AND NVL(lot_number, '-1') = NVL(p_lot, NVL(lot_number, '-1'));
SELECT mtrh.move_order_type
INTO l_hdr_type
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrh.header_id = mtrl.header_id;
SELECT 'Y'
INTO l_ret
FROM DUAL
WHERE EXISTS( SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND transaction_type_id IN (inv_globals.g_type_xfer_order_wip_issue, inv_globals.g_type_xfer_order_repl_subxfr));
SELECT move_order_type
INTO l_hdr_type
FROM mtl_txn_request_headers mtrh
WHERE mtrh.header_id = p_header_id;
SELECT distinct planning_group
INTO l_planning_group
FROM pjm_project_parameters ppov
WHERE project_id = nvl(p_project_id, 0)
AND organization_id = p_inventory_organization_id;
SELECT ppov.project_name, ppov.project_number, ppov.planning_group
INTO l_project_name, l_project_number, l_planning_group
FROM PJM_PROJECTS_ORG_V ppov
WHERE ppov.project_id = nvl(p_project_id, 0)
AND ppov.inventory_organization_id = p_inventory_organization_id;