[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msi.lot_control_code, msi.serial_number_control_code
FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
WHERE p_transaction_temp_id IS NOT NULL
AND mmtt.transaction_temp_id = p_transaction_temp_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
UNION ALL
SELECT msi.lot_control_code, msi.serial_number_control_code
FROM mtl_system_items msi, mtl_txn_request_lines mtrl
WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
AND mtrl.line_id = p_mo_line_id
AND msi.inventory_item_id = mtrl.inventory_item_id
AND msi.organization_id = mtrl.organization_id;
l_updated_count NUMBER := 0;
l_deleted_count NUMBER := 0;
UPDATE mtl_allocations_gtmp mat
SET (primary_quantity, transaction_quantity,secondary_quantity)
= (SELECT mat.primary_quantity - nvl(SUM(mtlt.primary_quantity),0)
, mat.transaction_quantity - nvl(SUM(mtlt.transaction_quantity),0)
, DECODE(NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
,0,NULL,
NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
) --INVCONV kkillams
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND mtlt.lot_number = mat.lot_number)
WHERE mat.transaction_temp_id = p_transaction_temp_id;
l_updated_count := SQL%ROWCOUNT;
DELETE mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id AND primary_quantity <= 0;
l_deleted_count := SQL%ROWCOUNT;
DELETE mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id
AND serial_number IN( SELECT msn.serial_number
FROM mtl_serial_numbers msn
WHERE msn.group_mark_id = p_transaction_temp_id);
l_deleted_count := SQL%ROWCOUNT;
DELETE mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id
AND serial_number IN( SELECT msn.serial_number
FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND msn.group_mark_id = mtlt.serial_transaction_temp_id);
l_deleted_count := SQL%ROWCOUNT;
UPDATE mtl_allocations_gtmp mat
SET (primary_quantity, transaction_quantity,secondary_quantity)
= (SELECT mat.primary_quantity - nvl(SUM(mtlt.primary_quantity),0)
, mat.transaction_quantity - nvl(SUM(mtlt.transaction_quantity),0)
, DECODE(NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
,0,NULL,
NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
) --INVCONV kkillams
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND mtlt.lot_number = mat.lot_number)
WHERE mat.transaction_temp_id = p_transaction_temp_id;
l_updated_count := SQL%ROWCOUNT;
DELETE mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id AND primary_quantity <= 0;
l_deleted_count := SQL%ROWCOUNT;
print_debug('# of Records Updated = ' || l_updated_count, l_api_name, g_info);
print_debug('# of Records Deleted = ' || l_deleted_count, l_api_name, g_info);
print_debug('Updated Temp Table to contain Unconfirmed Lots/Serials', l_api_name, g_info);
SELECT mmtt.inventory_item_id
, mmtt.transaction_uom
, mmtt.reservation_id
, msi.primary_uom_code
, msi.replenish_to_order_flag
, msi.bom_item_type
, mmtt.organization_id -- 9758641
, mmtt.lot_number -- 9758641
, msi.tracking_quantity_ind -- 9758641
, mmtt.move_order_line_id -- 9896283
, msi.secondary_uom_code --INVCONV kkillams
FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id;
SELECT primary_reservation_quantity, detailed_quantity
,secondary_reservation_quantity, secondary_detailed_quantity --INVCONV kkillams
FROM mtl_reservations
WHERE reservation_id = l_mmtt_info.reservation_id;
SELECT NVL(SUM(transaction_qty), 0) transaction_qty ,
NVL(SUM(secondary_transaction_qty), 0) secondary_transaction_qty
FROM mtl_available_inventory_temp
WHERE transaction_qty <> 0 ;
SELECT SUM(ABS(primary_quantity)), SUM(ABS(NVL(secondary_transaction_quantity, 0)))
INTO l_mmtt_primary_qty_sum,
l_mmtt_secondary_qty_sum
FROM mtl_material_transactions_temp
WHERE reservation_id= l_mmtt_info.reservation_id;
This is required since user can use select available inventory form to create new allocation
and update original allocation. Standard TMO form for sales order pick does not allow user
to enter brand new line on allocation block but user can use SAI form to do it. Hence the
need
*/
OPEN c_sai_info;
/*Bug:4700706. When the reservation record is deleted somehow by this time we need not
deal with the reservation.So we just return. */
RETURN;
print_debug('Final values to update reservation: ',l_api_name,g_info);
inv_reservation_pvt.update_reservation(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_api_version_number => 1.0
, p_original_rsv_rec => l_from_rsv_rec
, p_to_rsv_rec => l_to_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
);
l_insert_count NUMBER;
l_update_count NUMBER;
SELECT mmtt.transaction_header_id, mmtt.organization_id, mmtt.inventory_item_id, mmtt.transaction_uom, msi.primary_uom_code
, msi.secondary_uom_code --INVCONV kkillams
FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id;
SELECT lot_number, SUM(transaction_quantity) transaction_quantity ,SUM (primary_quantity) primary_quantity
,DECODE (SUM(NVL(secondary_quantity,0)),0,NULL,SUM(NVL(secondary_quantity,0))) secondary_quantity --INVCONV KKILLAMS
FROM mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id
GROUP BY lot_number;
inv_trx_util_pub.copy_insert_line_trx(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, x_new_txn_temp_id => x_new_txn_temp_id
, p_transaction_temp_id => p_transaction_temp_id
, p_organization_id => l_org_id
, p_txn_qty => l_rem_txn_qty
, p_primary_qty => l_rem_pri_qty
, p_sec_txn_qty => l_rem_sec_txn_qty --INVCONV KKILLAMS
);
INSERT INTO mtl_transaction_lots_temp(
transaction_temp_id
, lot_number, transaction_quantity, primary_quantity
, serial_transaction_temp_id, group_header_id
, last_update_date, last_updated_by, creation_date, created_by
,secondary_quantity --INVCONV kkillams
)
VALUES(
x_new_txn_temp_id
, curr_lot.lot_number,least(l_rem_txn_qty, l_lot_txn_qty), least(l_rem_pri_qty, l_lot_pri_qty)
, mtl_material_transactions_s.NEXTVAL, l_txn_header_id
, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
, DECODE(least(NVL(l_rem_sec_txn_qty,0), NVL(l_lot_sec_qty,0))
,0,NULL
,least(NVL(l_rem_sec_txn_qty,0), NVL(l_lot_sec_qty,0)))--INVCONV kkillams
)
RETURNING serial_transaction_temp_id, transaction_quantity, primary_quantity
,secondary_quantity --INVCONV kkillams
INTO l_serial_txn_temp_id, l_lot_txn_qty, l_lot_pri_qty
,l_lot_sec_qty; --INVCONV kkillams
print_debug('Lot Controlled Item. So Inserting MTLT', l_api_name, g_info);
INSERT INTO mtl_serial_numbers_temp(
transaction_temp_id
, fm_serial_number, to_serial_number, serial_prefix
, last_update_date, last_updated_by, creation_date, created_by
)
SELECT l_serial_txn_temp_id
, serial_number, serial_number, 1
, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
FROM mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = curr_lot.lot_number
AND ROWNUM <= l_lot_pri_qty;
l_insert_count := SQL%ROWCOUNT;
UPDATE mtl_serial_numbers
SET group_mark_id = l_serial_txn_temp_id
, line_mark_id = l_serial_txn_temp_id
WHERE serial_number IN (SELECT fm_serial_number FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_txn_temp_id)
AND inventory_item_id = l_item_id;
l_update_count := SQL%ROWCOUNT;
print_debug('Lot and Serial Controlled Item. So Inserting MSNT', l_api_name, g_info);
print_debug('# of Serials Inserted into MSNT = ' || l_insert_count, l_api_name, g_info);
print_debug('# of Serials Marked in MSN = ' || l_update_count, l_api_name, g_info);
INSERT INTO mtl_serial_numbers_temp(
transaction_temp_id
, fm_serial_number, to_serial_number, serial_prefix
, last_update_date, last_updated_by, creation_date, created_by
)
SELECT x_new_txn_temp_id
, serial_number, serial_number, 1
, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
FROM mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id
AND ROWNUM <= l_rem_pri_qty;
l_insert_count := SQL%ROWCOUNT;
UPDATE mtl_serial_numbers
SET group_mark_id = x_new_txn_temp_id
, line_mark_id= x_new_txn_temp_id
WHERE serial_number IN (SELECT fm_serial_number FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = x_new_txn_temp_id)
AND inventory_item_id = l_item_id;
l_update_count := SQL%ROWCOUNT;
print_debug('Serial Controlled Item. So Inserting MSNT', l_api_name, g_info);
print_debug('# of Serials Inserted into MSNT = ' || l_insert_count, l_api_name, g_info);
print_debug('# of Serials Marked in MSN = ' || l_update_count, l_api_name, g_info);
l_update_rsv BOOLEAN := FALSE;
/* -- For a Lot Controlled Item, MTLT would have been updated and so we need to consider that
-- while Querying for the Availability.
IF p_lot_number IS NOT NULL THEN
l_available_qty := l_available_qty - p_primary_quantity;
l_update_rsv := (l_reservation_count = 1);
If (l_update_rsv AND (p_reservation_id IS NOT NULL)) Then
l_existing_cc_res_pri_qty := l_reservations_tbl(1).primary_reservation_quantity;
IF( p_reservation_id is not null or l_update_rsv) THEN
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
, p_is_revision_control => (p_revision IS NOT NULL)
, p_is_lot_control => (p_lot_number IS NOT NULL)
, p_is_serial_control => FALSE
, p_demand_source_type_id => inv_reservation_global.g_source_type_cycle_count
, p_demand_source_header_id => -1
, p_demand_source_line_id => -1
, p_demand_source_name => NULL
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_lot_expiration_date => SYSDATE
, p_subinventory_code => p_subinventory_code
, p_locator_id => p_locator_id
, p_primary_quantity => p_primary_quantity
, p_secondary_quantity => p_secondary_quantity
, p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_grade_code => NULL
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
);
print_debug('Error from update quantity tree', l_api_name, g_info);
existing reservation is updated */
IF l_update_rsv THEN
l_cc_rsv_rec := l_reservations_tbl(1);
inv_reservation_pvt.update_reservation(
x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, p_original_rsv_rec => l_reservations_tbl(1)
, p_to_rsv_rec => l_cc_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
updated in the Reservation created now */
l_cc_rsv_rec.reservation_id := l_new_reservation_id;
inv_reservation_pvt.update_reservation(
x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, p_original_rsv_rec => l_existing_rsv_rec
, p_to_rsv_rec => l_cc_rsv_rec
, p_original_serial_number => l_dummy_sn
, p_to_serial_number => l_dummy_sn
, p_validation_flag => fnd_api.g_true
);
fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
SELECT mmtt.organization_id
, mmtt.inventory_item_id
, mmtt.reservation_id
, mmtt.revision
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_uom
, msi.primary_uom_code
, msi.secondary_uom_code --INVCONV kkillams
FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id;
SELECT lot_number
,SUM(transaction_quantity) transaction_quantity
,SUM(primary_quantity) primary_quantity
,DECODE(SUM(NVL(secondary_quantity,0)),0,NULL,SUM(NVL(secondary_quantity,0))) secondary_quantity --INVCONV kkillams
FROM mtl_allocations_gtmp
WHERE transaction_temp_id = p_transaction_temp_id
GROUP BY lot_number;
INSERT INTO mtl_allocations_gtmp(transaction_temp_id, lot_number, transaction_quantity, primary_quantity
,secondary_quantity) --INVCONV kkillams
SELECT p_transaction_temp_id, mtlt.lot_number, SUM(mtlt.transaction_quantity), SUM(mtlt.primary_quantity)
,DECODE(SUM(NVL(mtlt.secondary_quantity,0)),0,NULL,SUM(NVL(mtlt.secondary_quantity,0))) --INVCONV kkillams
FROM mtl_transaction_lots_temp mtlt
WHERE p_transaction_temp_id IS NOT NULL
AND mtlt.transaction_temp_id = p_transaction_temp_id
GROUP BY mtlt.lot_number
UNION ALL
SELECT mmtt.transaction_temp_id, mtlt.lot_number, SUM(mtlt.transaction_quantity), SUM(mtlt.primary_quantity)
,DECODE(SUM(NVL(mtlt.secondary_quantity,0)),0,NULL,SUM(NVL(mtlt.secondary_quantity,0))) --INVCONV kkillams
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
AND mmtt.move_order_line_id = p_mo_line_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
GROUP BY mmtt.transaction_temp_id, mtlt.lot_number;
INSERT INTO mtl_allocations_gtmp(transaction_temp_id, serial_number)
SELECT p_transaction_temp_id, msn.serial_number
FROM mtl_serial_numbers msn
WHERE p_transaction_temp_id IS NOT NULL
AND msn.group_mark_id = p_transaction_temp_id
UNION ALL
SELECT mmtt.transaction_temp_id, msn.serial_number
FROM mtl_material_transactions_temp mmtt, mtl_serial_numbers msn
WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
AND mmtt.move_order_line_id = p_mo_line_id
AND msn.group_mark_id = mmtt.transaction_temp_id;
INSERT INTO mtl_allocations_gtmp(transaction_temp_id, lot_number, serial_number, transaction_quantity, primary_quantity)
SELECT p_transaction_temp_id, mtlt.lot_number, msn.serial_number, 1, 1
FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
WHERE p_transaction_temp_id IS NOT NULL
AND mtlt.transaction_temp_id = p_transaction_temp_id
AND msn.group_mark_id = mtlt.serial_transaction_temp_id
UNION ALL
SELECT mmtt.transaction_temp_id, mtlt.lot_number, msn.serial_number, 1, 1
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
WHERE p_transaction_temp_id IS NULL and p_mo_line_id IS NOT NULL
AND mmtt.move_order_line_id = p_mo_line_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND msn.group_mark_id = mtlt.serial_transaction_temp_id;
DELETE mtl_allocations_gtmp;
PROCEDURE update_allocation_qty
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_transaction_temp_id NUMBER
, p_confirmed_quantity NUMBER
, p_transaction_uom VARCHAR2
--INVCONV kkillams
, p_sec_confirmed_quantity NUMBER
, p_secondary_uom_code VARCHAR2
--INVCONV kkillams
)
IS
l_api_name VARCHAR2(30) := 'UPDATE_ALLOCATION_QTY';
SELECT inventory_item_id, organization_id INTO l_inventory_item_id,l_organization_id
FROM mtl_material_transactions_temp WHERE transaction_temp_id = p_transaction_temp_id;
SELECT primary_uom_code INTO l_primary_uom FROM mtl_system_items
WHERE inventory_item_id =l_inventory_item_id
AND organization_id =l_organization_id;
UPDATE mtl_material_transactions_temp SET transaction_quantity =p_confirmed_quantity
, primary_quantity= l_confirmed_quantity_primary
--INVCONV kkillams
, secondary_uom_code = p_secondary_uom_code
, secondary_transaction_quantity = p_sec_confirmed_quantity
--END INVCONV kkillams
WHERE transaction_temp_id = p_transaction_temp_id;
END update_allocation_qty;