[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
remove_serial, update_lot, inv_trx_util_pub.insert_lot_trx. If proper care is
not exercised in calling them in conjunction, the state of allocations
can become invalid.
*/
g_pkg_name CONSTANT VARCHAR2(50):= 'eam_material_allocations_pvt';
select primary_quantity, transaction_quantity, move_order_line_id
into x_primary_mmtt_qty, x_tx_qty, x_move_order_line_id
from mtl_material_transactions_temp
where transaction_temp_id = p_transaction_temp_id;
update mtl_txn_request_lines
set quantity = quantity - p_qty_to_reduce,
quantity_detailed = quantity_detailed - p_qty_to_reduce,
line_status = decode(quantity,p_qty_to_reduce,5,line_status)
where line_id = p_move_order_line_id
returning txn_source_id, inventory_item_id
into l_txn_source_id, l_inventory_item_id;
update wip_requirement_operations
set quantity_allocated = quantity_allocated - p_qty_to_reduce
where wip_entity_id = l_txn_source_id
and inventory_item_id = l_inventory_item_id;
/* This procedure deletes a allocation and reduces the move order and WIP material
requirements by a corresponding amount. Thus this is to be used when the allocated
material is no longer wanted by the requestor.
*/
PROCEDURE delete_allocation(
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_transaction_temp_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_module constant varchar2(200) := g_module_name||'.delete_allocation';
SAVEPOINT DELETE_ALLOCATION;
'Calling inv_trx_util_pub.delete_transaction');
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => p_transaction_temp_id
);
'inv_trx_util_pub.delete_transaction',x_return_status,x_msg_count,x_msg_data);
ROLLBACK to delete_allocation;
ROLLBACK to delete_allocation;
end delete_allocation;
p_delete_remaining IN VARCHAR2,
x_new_transaction_temp_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_module constant varchar2(200) := g_module_name||'.reduce_allocation_header';
|| ',p_delete_remaining='|| p_delete_remaining || ')');
--Following elsif (3 lines): not sure whether we should allow delete here
--also or only in delete_allocation
--elsif (l_final_qty = 0) then
-- delete mtl_material_transactions_temp
-- where transaction_temp_id = p_transaction_temp_id;
update mtl_material_transactions_temp
set primary_quantity = l_final_qty, --following line converts between base to tx uom
transaction_quantity = l_final_qty * (l_tx_qty/l_primary_mmtt_qty)
where transaction_temp_id = p_transaction_temp_id;
if (p_delete_remaining is not null and (p_delete_remaining = 'Y')) then
--Reduce the allocation quantities in Move order headers, lines and in WRO
reduce_move_order(l_move_order_line_id, l_qty_to_reduce, 'Y');
'Calling inv_trx_util_pub.copy_insert_line_trx');
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_transaction_temp_id
, p_transaction_temp_id => p_transaction_temp_id
, p_organization_id => p_organization_id
, p_txn_qty => l_qty_to_reduce
, p_primary_qty => l_qty_to_reduce
);
'inv_trx_util_pub.copy_insert_line_trx',x_return_status,x_msg_count,x_msg_data);
'inv_trx_util_pub.copy_insert_line_trx returned '
|| 'x_new_txn_temp_id='|| x_new_transaction_temp_id);
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = p_new_transaction_temp_id
WHERE transaction_temp_id = p_transaction_temp_id
AND fm_serial_number = p_serial;
UPDATE mtl_serial_numbers
SET group_mark_id = p_new_transaction_temp_id
WHERE group_mark_id = p_transaction_temp_id
AND serial_number = p_serial;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id
and fm_serial_number = p_serial;
UPDATE mtl_serial_numbers
SET line_mark_id = -1
, group_mark_id = -1
, lot_line_mark_id = -1
WHERE group_mark_id = p_transaction_temp_id
AND serial_number = p_serial;
l_insert_count number;
l_update_count number;
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
)
VALUES(p_transaction_temp_id,p_serial, p_serial, 1
, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id);
l_insert_count := SQL%ROWCOUNT;
UPDATE mtl_serial_numbers
SET group_mark_id = p_transaction_temp_id
WHERE serial_number = p_serial
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
l_update_count := SQL%ROWCOUNT;
l_insert_count|| ' serials inserted into MSNT. '||l_update_count||
' serials marked in MSN');
1) Update lot quantity (if p_lot_quantity is non zero)
2) Delete a lot (if p_lot_quantity is zero)
3) Split a lot (if p_lot_quantity is less than current qty and new tx temp id is provided)
This API does not adjust the quantities at the MMTT or MSNT level and assumes
that they have been adjusted by another API call. Future work may need
to be done on the uniqueness of temp id, lot_number and qty combination.
*/
PROCEDURE update_lot(
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_transaction_temp_id IN NUMBER,
p_serial_transaction_temp_id IN NUMBER,
p_lot IN VARCHAR2,
p_lot_quantity IN NUMBER,
p_old_lot_quantity IN NUMBER,
p_new_transaction_temp_id IN NUMBER,
x_ser_trx_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_module constant varchar2(200) := g_module_name||'.update_lot';
l_update_count number := 0;
l_delete_count number := 0;
l_insert_ret_status number := null;
update mtl_transaction_lots_temp
set primary_quantity = p_lot_quantity,
transaction_quantity = p_lot_quantity
where serial_transaction_temp_id = p_serial_transaction_temp_id
and transaction_temp_id = p_transaction_temp_id
and lot_number = p_lot
and primary_quantity = p_old_lot_quantity;
update mtl_transaction_lots_temp
set primary_quantity = p_lot_quantity,
transaction_quantity = p_lot_quantity
where transaction_temp_id = p_transaction_temp_id
and lot_number = p_lot
and primary_quantity = p_old_lot_quantity
and rownum = 1;--update only one row since there is no PK in this table
l_update_count := SQL%ROWCOUNT;
l_update_count || ' row(s) updated in MTLT');
'Calling inv_trx_util_pub.insert_lot_trx('|| 'p_pri_qty=p_trx_qty='|| l_reduction_qty);
l_insert_ret_status := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => p_new_transaction_temp_id
, p_user_id => fnd_global.user_id
, p_lot_number => p_lot
, p_trx_qty => l_reduction_qty
, p_pri_qty => l_reduction_qty
, x_proc_msg => x_msg_data
, x_ser_trx_id => x_ser_trx_id);
'inv_trx_util_pub.insert_lot_trx returned: Status='||
l_insert_ret_status|| ' (0 success, -1 failure)'||
', Return Message='|| x_msg_data|| ', Serial Temp Id='||
x_ser_trx_id);
delete from mtl_transaction_lots_temp
where transaction_temp_id = p_transaction_temp_id
and lot_number = p_lot
and primary_quantity = p_old_lot_quantity
and rownum = 1;
l_delete_count := SQL%ROWCOUNT;
l_delete_count || ' row(s) deleted in MTLT');
end update_lot;
child records in MSNT can be inserted for this lot.
*/
PROCEDURE mark_lot_with_ser_temp_id(
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_transaction_temp_id IN NUMBER,
p_lot IN VARCHAR2,
p_primary_quantity IN NUMBER,
x_ser_trx_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_module constant varchar2(200) := g_module_name||'.mark_lot_with_ser_temp_id';
l_update_count number := 0;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into l_ser_trx_id from dual;
update mtl_transaction_lots_temp
set serial_transaction_temp_id = l_ser_trx_id
where transaction_temp_id = p_transaction_temp_id
and lot_number = p_lot
and primary_quantity = p_primary_quantity
and rownum = 1;
l_update_count := SQL%ROWCOUNT;
if (l_update_count = 1) then
x_return_status := FND_API.G_RET_STS_SUCCESS;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, l_update_count ||
' row(s) updated in MTLT with serial_transaction_temp_id: '|| x_ser_trx_id);