Inventory Close Period Notes - Uncosted or Pending Transactions Diagnostic SQL

CLOSE PERIOD NOTES - DIAGNOSTIC SQL SCRIPTS

When closing a period, Oracle will run the following scripts to checks tables for outstanding transactions in tables. Run these scripts or view the Pending form documented in  to identify what tables may hold stuck transactions.

***********************************************************
select count(*) from mtl_material_transactions
where costed_flag in ('N','E') and acct_period_id = &acct_period_id;

select count(*) from mtl_material_transactions_temp
where acct_period_id = &acct_period_id;

select count(*) from mtl_transactions_interface
where acct_period_id = &acct_period_id;

select count(*) from WIP_COST_TXN_INTERFACE
where acct_period_id = &acct_period_id;

select count(*) from WIP_MOVE_TXN_INTERFACE
where acct_period_id = &acct_period_id;

Uncosted Transactions

Run the following script to identify uncosted rows / errors in the MTL_MATERIAL_TRANSACTIONS table:

select
count(costed_flag) total,
costed_flag cflag,
substr(error_code,1,40) Code,
substr(error_explanation,1,100) Explan
from
mtl_material_transactions
having costed_flag IN ('E','N')
group by
costed_flag, error_code, error_explanation;

A costed flag of "E" will indicate that a cost worker request has completed with ERROR or WARNING. Find the effected cost worker request and examine the contents of the log file for an error.

Problem 1

If the following error message appears in the Material Transaction
Cost Worker:

APP-00001 cannot find message name inv_no_update. Uncosted material transactions in mtl_material_transactions table with costed_flag = E.

The transactions are WIP component transactions that do not corresponding rows in the MTL_MATERIAL_TXN_ALLOCATIONS and/or WIP_PERIOD_BALANCES  tables.

If these rows are missing you will need to run one of the following two scripts depending on the type of job the client using in WIP.

Discrete Jobs c276916.sql
Repetitive Jobs cm325424.sql

@SEE Note.1080033.6 for these scripts.

The script will recreate the necessary rows in the tables. Then reset flags and rerun the cost manager. (See Problem 2)

Problem 2

The cost manager or cost worker did not finish processing  all the transactions. Use the following sql script to reset transaction flags. The next time the cost manager runs the stuck rows will be processed.

update mtl_material_transactions
set costed_flag = 'N',
transaction_group_id = NULL
where costed_flag = 'E' or costed_flag = 'N'

Pending Transactions

Problem 1: mtl_transactions_interface

The following select statement will allow you review the  error code and error explanation for the errored transactions. Some of these can be fixed via the form and others will need to be fixed via sql.

select
count(process_flag) total,
process_flag pflag,
substr(error_code,1,40) Code,
substr(error_explanation,1,65) Explan
from
mtl_transactions_interface
group by
process_flag, error_code, error_explanation;

Note: common error APP-5372: transaction processor error

For Release 10.7 and 11.0:
The Material Transaction worker processes transactions in batch (based on the number in INV/SETUP/TRANSACTIONS/INTERFACE MANAGERS). If any 1 of the transactions in the batch do not pass validation then the whole batch will error. The 1 transaction that errored will be a valid error the others will have APP-5372. Correct the errored transaction and updated all transctions with the update statement below:

This update statement will reset the flags so that the next time the Material Transaction manager run it will process the transactions.

Note: Do not update records that have a process flag of 9. Those records are sales orders which have not come thru our interface yet. OE uses our interface and temp tables until the order has been ship confirmed or pick released.

The flags can be reset to allow processing the next time the transaction\ worker run by executing the followint sql script:

update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = 1,
error_code = NULL,
error_explanation = NULL
where process_flag IN (1,3);

Problem 2: mtl_material_transactions_temp

The following select statement will allow you review the error code and error explanation for the errored transactions. Some of these can be fixed via the form and others will need to be fixed via sql.

select
count(process_flag),
process_flag,
substr(error_code,1,40) Code,
substr(error_explanation,1,65) Explan
from
mtl_material_transactions_temp
group by
process_flag, error_code, error_explanation;

Note: common error APP-5372: transaction processor error

For Release 10.7 and 11: The Material Transaction worker processes transactions in batch (based on the number in INV/SETUP/TRANSACTIONS/INTERFACE MANAGERS) if any 1 of the transactions in the batch do not pass validation then the whole batch will error. The 1 transaction that errored will be a valid error the others will have APP-5372. Correct the errored transaction and updated all transctions with the below updated statement.

Note: common error APP-05075 transaction quantity cannot be zero

RMA's have been know to create these errored the following update statement will correct the transactions:

update mtl_material_transactions_temp
set primary_quantity = transaction_quantity
where transaction_quantity > 0 and process_flag <> '4';

Then run the update to reset the flags to reprocess the transactions.

This update statement will reset the flags so that the next time the Material Transaction manager run it will process the transactions.

Note: records with a process flag of 4 are OE transactions and should not be updated.

update mtl_material_transactions_temp
set process_flag = 'Y',
lock_flag = 'N',
transaction_mode = 3,
error_code = NULL,
error_explanation = NULL
where process_flag in ('Y','E')

Problem 3: wip_cost_txn_interface

The following updated statement will reset flags for any WIP cost transactions that are stuck in the WIP Cost Interface.

update wip_cost_txn_interface
set group_id = NULL,
transaction_id = NULL,
process_status = 1;

Problem 4: wip_move_txn_interface

The following updated statement will reset flags for any WIP move transactions that are stuck in the WIP Move Interface.

update wip_move_txn_interface
set group_id = NULL,
transaction_id = NULL,
process_status = 1;

References

NOTE:102878.1 - Inventory Transactions Guide
NOTE:1080033.6 - Erred Transactions in MTL_MATERIAL_TRANSACTIONS INV_NO_UPDATE / CST_INVALID_WIP

Attached

276916SQL (2.83 KB) 325424SQL (3.21 KB)