[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_header_id
, transaction_temp_id
, inventory_item_id
, revision
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, secondary_transaction_quantity
, lot_number
, lot_expiration_date
, serial_number
, transfer_to_location
, transaction_uom
, item_primary_uom_code
, secondary_uom_code
, nvl(fulfillment_base, 'P')
, organization_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_line_id;
SELECT fm_serial_number, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT request_number, grouping_rule_id
FROM mtl_txn_request_headers
WHERE header_id = l_trolin_tbl(1).header_id;
IS SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_move_order_line_id;
select primary_quantity, secondary_quantity, lot_number
from mtl_transaction_lots_temp
where transaction_temp_id = l_transaction_temp_id;
SELECT DECODE(NVL(mo_pick_confirm_required, 2), 1, 2, 2, 1, 1)
INTO l_auto_pick_confirm
FROM mtl_parameters
WHERE organization_id = l_trolin_tbl(1).organization_id;
SELECT auto_pick_confirm_flag INTO l_auto_pick_flag
FROM wsh_picking_batches
WHERE NAME = l_request_number;
UPDATE mtl_txn_request_lines
SET line_status = 5
WHERE line_id = l_trolin_tbl(1).line_id
AND NOT EXISTS (SELECT 1 FROM mtl_material_transactions_temp /*6120769Added NOT EXISTS condition*/
WHERE move_order_line_id = l_trolin_tbl(1).line_id
AND rownum<2 );
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET transaction_date = p_transaction_date
, acct_period_id = p_period_id
WHERE move_order_line_id = p_line_id;
print_debug('in update');
print_debug('calling delete details');
inv_replenish_detail_pub.delete_details(
p_transaction_temp_id => l_mold_tbl_temp(l_index).transaction_temp_id
, p_move_order_line_id => l_mold_tbl_temp(l_index).move_order_line_id
, p_reservation_id => l_mold_tbl_temp(l_index).reservation_id
, p_transaction_quantity => l_mold_tbl_temp(l_index).transaction_quantity
, p_transaction_quantity2 => l_mold_tbl_temp(l_index).secondary_transaction_quantity
, p_primary_trx_qty => l_mold_tbl_temp(l_index).primary_quantity
, x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
-- HW INVCONV - Update Qty2 fields
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_mold_tbl_temp(l_index).transaction_quantity
, quantity_delivered = quantity_delivered - l_mold_tbl_temp(l_index).transaction_quantity
, secondary_quantity_detailed = decode(secondary_quantity_detailed,FND_API.G_MISS_NUM, NULL, secondary_quantity_detailed - l_mold_tbl_temp(l_index).secondary_transaction_quantity)
, secondary_quantity_delivered =decode(secondary_quantity_delivered,FND_API.G_MISS_NUM, NULL, secondary_quantity_delivered - l_mold_tbl_temp(l_index).secondary_transaction_quantity)
WHERE line_id = l_mold_tbl_temp(l_index).move_order_line_id;
UPDATE mtl_txn_request_lines
SET line_status = 7
WHERE line_id = p_line_id;
/*SELECT wutta. honor_case_pick_flag into honor_case_pick
FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta
WHERE mmtt.standard_operation_id = wutta.user_task_type_id
AND mmtt.organization_id = wutta.organization_id
AND mmtt.transaction_temp_id = l_transaction_id(b);*/
SELECT count (*) into l_honor_case_pick_count
FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta
WHERE mmtt.standard_operation_id = wutta.user_task_type_id
AND mmtt.organization_id = wutta.organization_id
AND mmtt.transaction_temp_id = l_transaction_id(b)
AND honor_case_pick_flag = 'Y';
-- insert the records INV_AUTODETAIL.detail_row returns
-- into the mtl_material_transactions_temp table
--
-- Added Bug 3633141
-- Considering the Delivered Quantity for the move order line.
--
/* IF (p_move_order_type <> 3) THEN
-- HW INVCONV -Added Qty2
SELECT quantity_delivered, secondary_quantity_delivered
INTO l_detailed_qty, l_detailed_qty2
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
SELECT uom_code INTO l_detailed_uom
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
SELECT Count(*) INTO l_mtlt_count
FROM mtl_transaction_lots_temp WHERE
transaction_temp_id = l_transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = ABS(transaction_quantity)
, primary_quantity = ABS(primary_quantity)
, secondary_transaction_quantity = ABS(secondary_transaction_quantity)
WHERE transaction_temp_id = l_transaction_temp_id;
/*Bug#5140639. Added the below code to update distribution_account_id and
ship_to_location columns of the table MMTT*/
IF ( l_trolin_tbl(1).to_account_id IS NOT NULL) THEN
IF (l_debug = 1) THEN
print_debug('Updating distribution_account_id and ship_to_location_id in MMTT');
Update MTL_MATERIAL_TRANSACTIONS_TEMP
SET distribution_account_id = l_trolin_tbl(1).to_account_id,
ship_to_location = Nvl(l_trolin_tbl(1).ship_to_location_id, ship_to_location)
WHERE move_order_line_id = l_trolin_tbl(1).line_id;
print_debug('Number of rows updated:'||SQL%ROWCOUNT);
SELECT COUNT(*) INTO l_cnt_lot
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT lot_number
INTO l_lot_number
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT transaction_type_id
, transaction_action_id
INTO l_transaction_type_id
, l_transaction_action_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT DECODE(type_class, 1, 'Y', 'N')
INTO l_project_related
FROM mtl_transaction_types
WHERE transaction_type_id = l_transaction_type_id;
SELECT organization_id
INTO l_organization_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT project_id
, task_id
INTO l_project_id
, l_task_id
FROM mtl_txn_request_lines
WHERE line_id = (SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id);
UPDATE mtl_material_transactions_temp
SET source_project_id = l_project_id
, source_task_id = l_task_id
, pa_expenditure_org_id = l_organization_id
, expenditure_type = l_expenditure_type
WHERE transaction_temp_id = p_transaction_temp_id;
PROCEDURE delete_details(
p_transaction_temp_id IN NUMBER
, p_move_order_line_id IN NUMBER
, p_reservation_id IN NUMBER
, p_transaction_quantity IN NUMBER
, p_transaction_quantity2 IN NUMBER default FND_API.G_MISS_NUM
, p_primary_trx_qty IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_delete_temp_records IN BOOLEAN default TRUE /*Bug#5505709.*/
) IS
l_reservation_id NUMBER := p_reservation_id;
SELECT 1
, primary_uom_code
INTO l_ato_item
, l_primary_uom
FROM mtl_system_items
WHERE replenish_to_order_flag = 'Y'
AND bom_item_type = 4
AND inventory_item_id = l_mtl_reservation_tbl(1).inventory_item_id
AND organization_id = l_mtl_reservation_tbl(1).organization_id;
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_mtl_reservation_rec
, p_to_rsv_rec => l_mtl_reservation_tbl(1)
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
);
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_mtl_reservation_rec
, p_to_rsv_rec => l_mtl_reservation_tbl(1)
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
);
/*Bug#5505709. Put the code that deletes rows from MMTT/MSNT/MTLT inside the IF condition. This is
because, if this procedure is called from the 'Transact Move Order Line Allocations' form when the user
presses the DELETE button, deletion of these rows is already handled.*/
/*Bug10015753,the MSN mark label not removed caused by bug5505709,
since the issue ''FRM-40510: Oracle error: unable to DELETE record.' reported on bug5505709,
it caused by double MMTT deleting, we only need to add the condition on calling inv_mo_line_detail_util.delete_row from the 'Transact Move Order Line Allocations' form,
we should call clear_record API to remove the orphan records in MSNT/MTLT.
*/
CLEAR_RECORD(p_transaction_temp_id, l_success);
IF (p_delete_temp_records) THEN
inv_mo_line_detail_util.delete_row( x_return_status => l_return_status
, p_line_id => p_move_order_line_id
, p_line_detail_id => p_transaction_temp_id);
/* select count(1) into l_count
from mtl_material_transactions_temp
where move_order_line_id = p_move_order_line_id;
END IF; --p_delete_temp_records
END delete_details;
DELETE mtl_serial_numbers
WHERE group_mark_id = p_trx_header_id
AND current_status = 6;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
, line_mark_id = NULL
, lot_line_mark_id = NULL
WHERE group_mark_id = p_trx_header_id;
DELETE mtl_serial_numbers_temp
WHERE group_header_id = p_trx_header_id;
DELETE mtl_transaction_lots_temp
WHERE group_header_id = p_trx_header_id;
DELETE mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_header_id;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = trx_temp_id;
SELECT fm_serial_number
, to_serial_number
, group_header_id
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = trx_temp_id;
SELECT COUNT(*)
INTO l_lot_count
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_tmp_id;
SELECT transaction_header_id
INTO l_transaction_header_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_trx_tmp_id;
SELECT COUNT(*)
INTO l_serial_count
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_temp_id;
* and do not use mmtt.transaction_header_id in the WHERE clause of the UPDATE statement
*/
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE (group_mark_id = l_serial_temp_id OR group_mark_id = l_header_id) --Bug#6009436.
AND serial_number >= NVL(l_fm_serial_number, serial_number)
AND serial_number <= NVL(l_to_serial_number, NVL(l_fm_serial_number, serial_number))
AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_temp_id;
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_tmp_id;
SELECT COUNT(*)
INTO l_serial_count
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_trx_tmp_id;
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE ( group_mark_id = p_trx_tmp_id OR group_mark_id = l_header_id ) --Bug#6009436
AND serial_number >= NVL(l_fm_serial_number, serial_number)
AND serial_number <= NVL(l_to_serial_number, NVL(l_fm_serial_number, serial_number))
AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_trx_tmp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_next_id
FROM DUAL;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_mmtt_rec.organization_id
AND inventory_item_id = l_mmtt_rec.inventory_item_id;
inv_mo_line_detail_util.insert_row(x_return_status => l_return_status, p_mo_line_detail_rec => l_mmtt_rec);
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mmtt_rec.move_order_line_id;
SELECT lot_number
, primary_quantity
, transaction_quantity
, serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT serial_number
FROM mtl_unit_transactions
WHERE transaction_id = DECODE(l_lot_control_code, 1, l_transaction_id, l_serial_trx_id);
SELECT reservable_type
INTO l_reservable_type
FROM mtl_secondary_inventories
WHERE organization_id = l_mmtt_rec.organization_id
AND secondary_inventory_name = p_new_subinventory;
print_debug('not reservable staging subinventory, delete org wide reservation');
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_mtl_reservation_tbl(1)
, p_to_rsv_rec => l_mtl_reservation_rec
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
, p_validation_flag => fnd_api.g_true
);
print_debug('after update reservation return status is ' || l_return_status);
SELECT COUNT(transaction_temp_id)
INTO l_lot_count
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
SELECT txn_source_line_id, inventory_item_id, transaction_source_type_id
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
SELECT source_line_id, inventory_item_id
FROM wsh_delivery_details
WHERE move_order_line_id = p_line_id;
SELECT quantity
FROM mtl_txn_request_lines
WHERE inventory_item_id = l_inventory_item_id
AND line_status <> 5
AND txn_source_line_id IN(SELECT line_id
FROM oe_order_lines_all
WHERE line_set_id = l_line_set_id)
FOR UPDATE OF quantity NOWAIT;
SELECT quantity
FROM mtl_txn_request_lines
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id --bug 7012974 performance issue in TMO
AND line_status <> 5
AND txn_source_line_id = l_txn_source_line_id
FOR UPDATE OF quantity NOWAIT;
SELECT organization_id
INTO l_organization_id
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
SELECT NVL(required_quantity, quantity), NVL(quantity_delivered, 0) , NVL(secondary_required_quantity, secondary_quantity) ,NVL(secondary_quantity_delivered, 0)
INTO l_mo_quantity, l_quantity_delivered, l_mo_quantity2, l_quantity_delivered2
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
SELECT NVL(SUM(ABS(transaction_quantity)), 0), NVL(SUM(ABS(secondary_transaction_quantity)), 0)
INTO l_allocation_quantity , l_allocation_quantity2
FROM mtl_material_transactions_temp
WHERE move_order_line_id <> p_line_id
AND inventory_item_id = l_inventory_item_id
AND transaction_action_id = 28
AND trx_source_line_id = l_txn_source_line_id;
SELECT mmtt.organization_id
, mtrh.move_order_type
INTO l_org_id
, l_move_order_type
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND mmtt.transaction_temp_id = p_transaction_temp_id;
SELECT OVPK_TRANSFER_ORDERS_ENABLED
INTO l_temp
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT wip_overpick_enabled
INTO l_temp
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT trx_source_line_id
, transaction_quantity
, move_order_line_id
, secondary_transaction_quantity
INTO l_trx_source_line_id
, l_this_alloc
, l_mo_line_id
, l_this_alloc2
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT SUM(transaction_quantity) , sum(secondary_transaction_quantity)
INTO l_all_alloc , l_all_alloc2
FROM mtl_material_transactions_temp
WHERE trx_source_line_id = l_trx_source_line_id;
SELECT organization_id
, transaction_quantity
, move_order_line_id
, allocated_lpn_id
, secondary_transaction_quantity
, trx_source_line_id
, transaction_source_type_id
, nvl(fulfillment_base, 'P')
INTO l_org_id
, l_this_alloc
, l_mo_line_id
, l_alloc_lpn_id
, l_this_alloc2
, l_source_line_id
, l_txn_source_type_id
, l_fulfill_base
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
/*SELECT serial_number_control_code
, lot_control_code
, revision_qty_control_code
INTO l_ser_code
, l_lot_code
, l_rev_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = l_org_id;
/* UPDATE mtl_txn_request_lines
SET quantity_detailed = l_this_alloc + p_overpicked_qty
WHERE line_id = l_mo_line_id;
print_debug('OVPK: Updated quantity_detailed column in MTRL to ' || l_this_alloc || '+' || p_overpicked_qty);
SELECT organization_id
, transaction_quantity
, move_order_line_id
INTO l_org_id
, l_this_alloc
, l_mo_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT serial_number_control_code
, lot_control_code
, revision_qty_control_code
INTO l_ser_code
, l_lot_code
, l_rev_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = l_org_id;
/* UPDATE mtl_txn_request_lines
SET quantity_detailed = l_this_alloc + p_overpicked_qty
WHERE line_id = l_mo_line_id;
print_debug('OVPK: Updated quantity_detailed column in MTRL to ' || l_this_alloc || '+' || p_overpicked_qty);
SELECT mmtt.organization_id
, mtrh.move_order_type
INTO l_org_id
, l_move_order_type
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND mmtt.transaction_temp_id = p_transaction_temp_id;
SELECT OVPK_TRANSFER_ORDERS_ENABLED
INTO l_temp
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT wip_overpick_enabled
INTO l_temp
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT trx_source_line_id
, transaction_quantity
, move_order_line_id
, secondary_transaction_quantity -- MUOM Fulfillment Project
INTO l_trx_source_line_id
, l_this_alloc
, l_mo_line_id
, l_this_alloc2
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT SUM(transaction_quantity), SUM(SECONDARY_TRANSACTION_QUANTITY)
INTO l_all_alloc, l_all_alloc2
FROM mtl_material_transactions_temp
WHERE trx_source_line_id = l_trx_source_line_id;
SELECT nvl(SUM(transaction_quantity),0), nvl(SUM(SECONDARY_QUANTITY),0)
INTO l_all_mtlt, l_all_mtlt2
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT nvl(SUM(transaction_quantity),0), nvl(SUM(SECONDARY_QUANTITY),0)
INTO l_this_mtlt, l_this_mtlt2
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_num;
SELECT organization_id
, transaction_quantity
, move_order_line_id
, secondary_transaction_quantity -- MUOM Fulfillment Project
, nvl(fulfillment_base, 'P')
INTO l_org_id
, l_this_alloc
, l_mo_line_id
, l_this_alloc2
, l_fulfill_base
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
/* SELECT serial_number_control_code
, lot_control_code
, revision_qty_control_code
INTO l_ser_code
, l_lot_code
, l_rev_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = l_org_id;
/* UPDATE mtl_txn_request_lines
SET quantity_detailed = l_this_alloc + p_overpicked_qty
WHERE line_id = l_mo_line_id;
print_debug('OVPK: Updated quantity_detailed column in MTRL to ' || l_this_alloc || '+' || p_overpicked_qty);
print_debug('OVPK: Updated quantity_detailed2 column in MTRL to ' || l_this_alloc2 || '+' || p_overpicked_qty2);