[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
, 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 SUM(ABS(primary_quantity))
INTO l_mmtt_primary_qty_sum
FROM mtl_material_transactions_temp
WHERE reservation_id= l_mmtt_info.reservation_id;
/*Bug:4700706. When the reservation record is deleted somehow by this time we need not
deal with the reservation.So we just return. */
RETURN;
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;
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;