[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mol.line_id, mol.line_number
FROM mtl_txn_request_headers moh,
mtl_txn_request_lines mol
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = INV_GLOBALS.g_move_order_put_away
AND mol.organization_id = p_organization_id
AND mol.lpn_id is not null
AND mol.lpn_id = NVL(p_lpn_id, mol.lpn_id)
AND (NVL(mol.quantity_delivered,0) + NVL(mol.quantity_detailed,0)) <
NVL(mol.quantity,0)
FOR UPDATE;
SELECT
distinct mol.lpn_id,
lpn.license_plate_number,
lpn.lpn_context, --- for bug 5175569
mol.line_id,
mol.txn_source_id --- for bug 7190056
FROM
mtl_txn_request_headers moh,
mtl_txn_request_lines mol,
wms_license_plate_numbers lpn
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = INV_GLOBALS.g_move_order_put_away
AND mol.organization_id = p_organization_id
AND mol.lpn_id is not null
AND mol.lpn_id = nvl(p_lpn_id, mol.lpn_id) --BUG3497572 p_lpn_id is an optional argument for concurrent request
AND mol.lpn_id = lpn.lpn_id
AND (NVL(mol.quantity_delivered,0) + NVL(mol.quantity_detailed,0)) <
NVL(mol.quantity,0)
ORDER BY mol.txn_source_id ASC; --bug 6189438,6160359,6716184,7190056
-- select operation_plan_ID from MMTT
-- This makes this file depending on patchset I and above.
-- We need to at least break the dual maintanance between H and I,
-- but can keep the dual maintanance between I and J
CURSOR mmtt_cursor (l_line_id IN NUMBER) IS
SELECT
nvl(mmtt.LPN_ID, mmtt.CONTENT_LPN_ID),
mmtt.organization_id, -- Added for bug # 4964866
mmtt.subinventory_code, -- Added for bug # 4964866
mmtt.locator_id, -- Added for bug # 4964866
mmtt.transaction_temp_id,
mmtt.operation_plan_id, -- added for ATF_J
mol.backorder_delivery_detail_id, -- added for ATF_J
mol.crossdock_type -- added for ATF_J
FROM
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol
WHERE mmtt.move_order_line_id = l_line_id
AND mol.line_id = l_line_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id
AND organization_id = p_organization_id
FOR UPDATE nowait;
SELECT 1
INTO l_process_flag_count
FROM DUAL WHERE exists
(SELECT 1
FROM mtl_txn_request_lines
WHERE lpn_id = p_lpn_id
AND line_status <> inv_globals.g_to_status_closed /* 3867448 */
AND Nvl(wms_process_flag, 1) = 2);
SELECT pregen_putaway_tasks_flag
INTO l_pregen_putaway_tasks_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;
UPDATE mtl_txn_request_lines
SET
last_update_date = Sysdate,
quantity_detailed = (SELECT SUM(transaction_quantity)
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mol_line_id)
WHERE line_id = l_mol_line_id;
SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
INTO l_discrepancy
FROM mtl_txn_request_lines mtrl,
(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) wlpn
WHERE mtrl.lpn_id = wlpn.lpn_id
AND mtrl.line_status = 7
AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) <> Nvl(mtrl.quantity_detailed,0)
AND mtrl.organization_id = p_organization_id;
SELECT transaction_temp_id,
locator_id,
inventory_item_id,
primary_quantity,
operation_plan_id -- added for ATF_J2
FROM mtl_material_transactions_temp
WHERE lpn_id = p_lpn_id
AND move_order_line_id = Nvl(p_move_order_line_id, move_order_line_id) -- added for ATF_J2
AND organization_id = p_org_id;
DELETE
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE lpn_id = p_lpn_id
AND move_order_line_id = Nvl(p_move_order_line_id, move_order_line_id) -- added for ATF_J2
AND organization_id = p_org_id);
-- MMTT and MOL update from inbound UI for item load.
);
DELETE
FROM mtl_material_transactions_temp
WHERE lpn_id = p_lpn_id
AND move_order_line_id = Nvl(p_move_order_line_id, move_order_line_id) -- added for ATF_J2
AND organization_id = p_org_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = Nvl(quantity_delivered, 0)
WHERE lpn_id = p_lpn_id
AND line_id = Nvl(p_move_order_line_id, line_id) -- added for ATF_J2
AND organization_id = p_org_id;