Checked for relevance on 06-Dec-2007
DISCLAIMER: This script is provided for educational purposes only. It is not supported
by Oracle World Wide Technical Support. The script has been tested and
appears to works as intended. However, you should always test any script
before relying on it.
Proofread this script prior to running it! Due to differences in the way text
editors, email packages and operating systems handle text formatting (spaces,
tabs and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
WARNING: This script is being updated periodically. Please download it from Metalink
every time you need to run it in order to have the latest version.
REM********************************************************************************
REM Abstract: Close Period Notes - Diagnostic SQL Scripts
REM cst_close_per.sql
REM Date: 08/10/2001
Description: The following SQL scripts are designed to assist in the closing of an inventory period. They will help identify errors, resolve common errors, and reset flags so that the transactions may be reprocessed. Use these scripts in conjunction with Note 102878.1.
EMail: pj.arriza
Internal_Only: N
Keywords: period close, mtl_material_transactions, stuck, Uncosted Transactions
Metalink_Note: 143004.1
Product: APPS
URL:
Version: 1.0
Product_Ver: 11.5.x
Product_Code: 569
Subcomponent: PRD
Category: Diagnostics
Framework: StandAlone
--------------------------------------------------------------
/*
*********************************************************************
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;
/*
*********************************************************************
Note: only release 11.5 will show anything in error_code or 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 cm276916.sql
Repetitive Jobs cm325557.sql
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,
transaction_set_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;
Diagnostic Scripts: Close Period Notes - Diagnostic SQL Scripts
最新推荐文章于 2024-04-30 15:35:14 发布