Total lot transaction quantity does not match transaction quantity|ORA-06502: PL/SQL: numeric or value error: character string buffer too small in Pa"

Oracle Inventory Pending Transaction error message "Total lot transaction quantity does not match transaction quantity|ORA-06502: PL/SQL: numeric or value error: character string buffer too small in Pa" "


Inventory or Manufacturing related transaction has been completed, but not updated in Material transaction inquiry form due to data in Pending transaction with this error.


Normally transaction Available in "MTL_MATERIAL_TRANSACTIONS_TEMP" and "MTL_TRANSACTION_LOTS_TEMP" table. Some time data is not properly updated in "MTL_TRANSACTION_LOTS_TEMP" table, this Error message will display.

Oracle Recommended  Data Fix.

1. Script to update the transaction quantity for a specific lot number.


update mtl_transaction_lots_temp
set TRANSACTION_QUANTITY = &trans_qty
PRIMARY_QUANTITY = &prim_qty
where TRANSACTION_TEMP_ID = &trans_id
and lot_number = '&lot_no';

2. Script to add a lot if the lot data is completely missing:

insert into mtl_transaction_lots_temp
(TRANSACTION_TEMP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
LOT_NUMBER)
select
Transaction_temp_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
:TRANS_QTY,
:PRIM_QTY,
:LOT_NUMBER
from mtl_material_transactions_temp
where transaction_temp_id = :temp_id;

After inserted the lines in "MTL_TRANSACTION_LOTS_TEMP" table, "Resubmit" the Pending Transactions.