The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mmtt.transaction_temp_id
, ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
, ABS(mmtt.secondary_transaction_quantity) -- Added for bug 8703085
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mol_id
AND NOT EXISTS(
SELECT wdt.transaction_temp_id
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.transaction_temp_id IS NOT NULL
AND wdt.transaction_temp_id <> p_temp_id);
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_tmp_id;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_sn_temp_id;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(transaction_temp_id)
INTO cnt
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
SELECT move_order_line_id
, organization_id
, inventory_item_id
, content_lpn_id
, transfer_lpn_id
, wms_task_type
, lpn_id --BUG 12670785 Unload problem
, subinventory_code -- Added for 14699845 (Flexible Lot Allocation)
, locator_id -- Added for 14699845 (Flexible Lot Allocation)
, revision -- Added for 14699845 (Flexible Lot Allocation)
, allocated_lpn_id -- Added for 14699845 (Flexible Lot Allocation)
INTO mol_id
, l_org_id
, l_item_id
, l_content_lpn_id
, l_transfer_lpn_id
, l_wms_task_types
, l_lpn_id --BUG 12670785 Unload problem
, l_subinventory -- Added for 14699845 (Flexible Lot Allocation)
, l_locator_id -- Added for 14699845 (Flexible Lot Allocation)
, l_revision -- Added for 14699845 (Flexible Lot Allocation)
, l_allocated_lpn_id -- Added for 14699845 (Flexible Lot Allocation)
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT line_status
INTO line_status
FROM mtl_txn_request_lines
WHERE line_id = mol_id;
, p_quantity_to_delete => l_quantity
);
mydebug(' alloc quantity deleted ' || l_del_quantity);
mydebug(' alloc quantity deleted ' || l_sec_del_quantity);
UPDATE mtl_txn_request_lines
SET quantity_detailed =(quantity_detailed - l_del_quantity),
secondary_quantity_detailed =(secondary_quantity_detailed - l_sec_del_quantity)
WHERE line_id = mol_id;
mydebug('updated mol:' || mol_id);
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
mydebug('deleted from wms_dispatched_tasks ');
SELECT COUNT(transaction_temp_id)
INTO cnt
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mol_id;
UPDATE mtl_txn_request_lines
SET line_status = inv_globals.g_to_status_closed
WHERE line_id = mol_id;
mydebug(' updated the mo line status to ' || inv_globals.g_to_status_closed);
SELECT msi.lot_control_code
, msi.serial_number_control_code
, NVL(mmtt.serial_allocated_flag,'N') --HWSNIssue 13860546 --Latent Issue
INTO v_lot_control_code
, v_serial_control_code
, v_allocate_serial_flag
FROM mtl_system_items msi
, mtl_material_transactions_temp mmtt
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT organization_id
INTO l_org_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT NVL(mr.lot_number, mtrl.lot_number)
INTO l_reserved_lot
FROM mtl_material_transactions_temp mmtt,
mtl_reservations mr,
mtl_txn_request_lines mtrl
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = l_org_id
AND mmtt.reservation_id = mr.reservation_id (+)
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.organization_id = mmtt.organization_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
--Bug 2940878 fix added org and item restriction
AND current_organization_id = l_org_id
AND inventory_item_id = l_item_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
--Bug 2940878 fix added org and item restriction
AND current_organization_id = l_org_id
AND inventory_item_id = l_item_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_transaction_temp_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id);
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = NULL
WHERE transaction_temp_id = p_temp_id;
mydebug(' update done ');
UPDATE mtl_material_transactions_temp
SET lpn_id = NULL
, content_lpn_id = NULL
, transfer_lpn_id = NULL
, wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
WHERE transaction_temp_id = p_temp_id;
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
mydebug('deleted WDT with temp_id ' || p_temp_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(1)
INTO l_count
FROM mtl_material_transactions_temp
WHERE transfer_lpn_id = l_transfer_lpn_id;
SELECT lpn_context INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_transfer_lpn_id;
select count(1) into l_wlc_count from wms_lpn_contents where parent_lpn_id = l_transfer_lpn_id and organization_id = l_org_id;
SELECT 'CANCELLED'
, mmtt.transaction_temp_id
, ABS(mmtt.transaction_quantity)
, mmtt.transaction_uom
, mmtt.move_order_line_id
, mtrl.uom_code
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.parent_line_id = p_temp_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 9
UNION ALL
SELECT 'OVERPICKED'
, mmtt.transaction_temp_id
, ABS(mmtt.transaction_quantity)
, mmtt.transaction_uom
, to_number(NULL)
, to_char(NULL)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.transaction_temp_id <> mmtt.parent_line_id
AND mmtt.transaction_action_id = 2
AND mmtt.move_order_line_id IS NULL;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_sn_temp_id;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id;
SELECT m.transaction_temp_id
, 1 dummy_sort
FROM wms_dispatched_tasks w
, mtl_material_transactions_temp m
WHERE m.transfer_lpn_id = p_xfer_lpn_id
AND m.transaction_temp_id <> p_temp_id
AND m.transaction_temp_id = m.parent_line_id
AND w.transaction_temp_id = m.transaction_temp_id
AND w.status = 4
AND EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp m2
WHERE m2.transfer_lpn_id = m.transfer_lpn_id
AND m2.organization_id = m.organization_id
AND m2.transaction_temp_id = m2.parent_line_id
AND m2.transaction_temp_id <> m.transaction_temp_id
AND m2.transaction_temp_id <> p_temp_id
AND m2.content_lpn_id = m.transfer_lpn_id
)
UNION ALL
-- Content LPNs
SELECT m.transaction_temp_id
, 2 dummy_sort
FROM wms_dispatched_tasks w
, mtl_material_transactions_temp m
WHERE m.transfer_lpn_id = p_xfer_lpn_id
AND m.transaction_temp_id <> p_temp_id
AND m.transaction_temp_id = m.parent_line_id
AND w.transaction_temp_id = m.transaction_temp_id
AND w.status = 4
AND m.content_lpn_id IS NOT NULL
UNION ALL
-- Material unpacked from content LPNs
SELECT m.transaction_temp_id
, 3 dummy_sort
FROM wms_dispatched_tasks w
, mtl_material_transactions_temp m
WHERE m.transfer_lpn_id = p_xfer_lpn_id
AND m.transaction_temp_id <> p_temp_id
AND m.transaction_temp_id = m.parent_line_id
AND w.transaction_temp_id = m.transaction_temp_id
AND w.status = 4
AND EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp m2
WHERE m2.transfer_lpn_id = m.transfer_lpn_id
AND m2.organization_id = m.organization_id
AND m2.transaction_temp_id = m2.parent_line_id
AND m2.transaction_temp_id <> m.transaction_temp_id
AND m2.transaction_temp_id <> p_temp_id
AND m2.content_lpn_id = m.lpn_id
)
UNION ALL
-- All other picked material
SELECT m.transaction_temp_id
, 4 dummy_sort
FROM wms_dispatched_tasks w
, mtl_material_transactions_temp m
WHERE m.transfer_lpn_id = p_xfer_lpn_id
AND m.transaction_temp_id <> p_temp_id
AND m.transaction_temp_id = m.parent_line_id
AND w.transaction_temp_id = m.transaction_temp_id
AND w.status = 4
AND m.content_lpn_id IS NULL
AND ( (m.lpn_id IS NOT NULL
AND NOT EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp m2
WHERE m2.transfer_lpn_id = m.transfer_lpn_id
AND m2.organization_id = m.organization_id
AND m2.transaction_temp_id = m2.parent_line_id
AND m2.transaction_temp_id <> m.transaction_temp_id
AND m2.transaction_temp_id <> p_temp_id
AND m2.content_lpn_id = m.lpn_id
)
)
OR m.lpn_id IS NULL
)
AND NOT EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp m3
WHERE m3.transfer_lpn_id = m.transfer_lpn_id
AND m3.organization_id = m.organization_id
AND m3.transaction_temp_id = m3.parent_line_id
AND m3.transaction_temp_id <> m.transaction_temp_id
AND m3.transaction_temp_id <> p_temp_id
AND m3.content_lpn_id = m.transfer_lpn_id
)
ORDER BY dummy_sort;
l_parent_deleted BOOLEAN := FALSE;
SELECT organization_id
, inventory_item_id
, content_lpn_id
, transfer_lpn_id
, move_order_line_id -- Added for 14699845 (Flexible Lot Allocation)
, subinventory_code -- Added for 14699845 (Flexible Lot Allocation)
, locator_id -- Added for 14699845 (Flexible Lot Allocation)
, revision -- Added for 14699845 (Flexible Lot Allocation)
INTO l_org_id
, l_item_id
, l_content_lpn_id
, l_transfer_lpn_id
, l_mol_id -- Added for 14699845 (Flexible Lot Allocation)
, l_subinventory -- Added for 14699845 (Flexible Lot Allocation)
, l_locator_id -- Added for 14699845 (Flexible Lot Allocation)
, l_revision -- Added for 14699845 (Flexible Lot Allocation)
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_txn_temp_id;
, p_quantity_to_delete => l_quantity
);
inv_trx_util_pub.delete_transaction
( x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_transaction_temp_id => l_temp_id
, p_update_parent => TRUE
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = (quantity_detailed - l_conv_qty)
WHERE line_id = l_mo_line_id;
mydebug('Updated mol: ' || to_char(l_mo_line_id));
SELECT COUNT(transaction_temp_id)
INTO l_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_id;
UPDATE mtl_txn_request_lines
SET line_status = inv_globals.g_to_status_closed
WHERE line_id = l_mo_line_id;
SELECT msi.lot_control_code
, msi.serial_number_control_code
, NVL(mmtt.serial_allocated_flag,'N') -- Modified for 14699845 (Flexible Lot Allocation) Added NVL Latent
INTO v_lot_control_code
, v_serial_control_code
, v_allocate_serial_flag
FROM mtl_system_items msi
, mtl_material_transactions_temp mmtt
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_txn_temp_id;
mydebug('Parent MMTT deleted when cancelled child tasks were processed');
l_parent_deleted := TRUE;
IF NOT l_parent_deleted THEN
IF (l_debug = 1) THEN
mydebug(' lot code ' || v_lot_control_code);
SELECT organization_id
INTO l_org_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_txn_temp_id;
SELECT mmttc.move_order_line_id
INTO l_mol_id
FROM mtl_material_transactions_temp mmttc, mtl_material_transactions_temp mmttp
WHERE mmttc.parent_line_id = mmttp.transaction_temp_id
AND mmttc.parent_line_id <> mmttc.transaction_temp_id
AND mmttc.organization_id = mmttp.organization_id
AND mmttc.organization_id = l_org_id
AND mmttp.transaction_temp_id = p_txn_temp_id
AND mmttp.transaction_temp_id = mmttp.parent_line_id
AND ROWNUM = 1;
SELECT mr.lot_number
INTO l_reserved_lot
FROM mtl_material_transactions_temp mmttc, mtl_material_transactions_temp mmttp,
mtl_reservations mr
WHERE mmttc.parent_line_id = mmttp.transaction_temp_id
AND mmttc.transaction_temp_id <> mmttc.parent_line_id
AND mmttc.organization_id = l_org_id
AND mmttc.reservation_id = mr.reservation_id (+)
AND mmttp.transaction_temp_id = p_txn_temp_id
AND mmttp.parent_line_id = mmttp.transaction_temp_id
AND mmttp.organization_id = mmttc.organization_id
AND ROWNUM = 1;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
AND current_organization_id = l_org_id
AND inventory_item_id = l_item_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_txn_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
AND current_organization_id = l_org_id
AND inventory_item_id = l_item_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_transaction_temp_id;
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = NULL
WHERE transaction_temp_id = p_txn_temp_id;
mydebug('Updated MTLT');
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_txn_temp_id;
UPDATE mtl_material_transactions_temp
SET lpn_id = NULL
, content_lpn_id = NULL
, transfer_lpn_id = NULL
, wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
WHERE parent_line_id = p_txn_temp_id;
END IF; -- end if parent not deleted
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp
WHERE transfer_lpn_id = l_transfer_lpn_id;
SELECT lpn_context INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_transfer_lpn_id;
DELETE FROM MTL_TRANSACTION_LOTS_TEMP
WHERE transaction_temp_id IN (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE parent_line_id = p_txn_temp_id);
DELETE FROM MTL_TRANSACTION_LOTS_TEMP
WHERE transaction_temp_id = p_txn_temp_id;
mydebug('Deleted '||SQL%ROWCOUNT||' rows from MTLT');
SELECT mr.lot_number
INTO l_reserved_lot
FROM mtl_material_transactions_temp mmtt, mtl_reservations mr
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = p_org_id
AND mmtt.reservation_id = mr.reservation_id;