Common scripts for work-in-process and cost troubleshooting

1. Are you sure you want to deal with in-process materials?

These scripts are provided only by the Oracle Support Service for troubleshooting purposes. These scripts have been tested and run as expected. However, you should always perform the test before relying on any script. Verify the script before running it!

Because different file editors, email packages, and operating systems process text formats (spaces, tabs, and carriage returns), these scripts may be in an unexecutable state when you receive them for the first time.

Check this script to make sure that this type of error is corrected.


First, check whether the inventory transaction processing manager is running. If there are any pending or error records in the transaction processing interface in the list. These images can be queried through the query inventory transaction processing interface. Navigation inventory/transaction processing/open transaction processing interfaces.
Records can be deleted and/or resubmitted to this table.

Before checking the transaction processing to be determined, navigate to inventory/transaction processing to be determined.
Record can only be resubmitted using this form.
If there are pending transactions that are stuck in the mtl_material_transactions_temp table, and transaction_type_id = 5, (reverse/in-process transaction processing) and proces_flag = E, they need to be committed.
To investigate why the transaction failed, run the following SQL script:

 

select transaction_source_id , inventory_item_id, process_flag,error_code, error_explanation,transaction_source_type_id, organization_idfrom mtl_material_transactions_tempwhere process_flag = ?E?and transaction_source_id= ; 

Use this script to resubmit the transaction processing:

update mtl_material_transactions_tempset process_flag = ‘Y‘,lock_flag = ‘N‘,transaction_mode= 3,error_code = NULL,error_explaination = NULLwhere process_flag = ‘E‘and transaction_source_id= ; 

2. The transaction is to be processed in work-in-process.

There are two ways to complete transaction processing.

In-product mobile transfer Transaction Processing Form: (navigate to "-> In-Product/mobile transaction processing/mobile transaction, set the transaction type to complete ).

In-process Raw Material Transaction Processing Form: (navigation "-> In-process/Raw Material Transaction Processing/completion Transaction Processing)

A record is used to create a finished Assembly product.
They are trapped in the mtl_material_transactions_temp table. (Navigate to "-> Inventory/transaction processing/TBD Transaction Processing)" error message: or run the following SQL script:

Run this script to find organization_id:

select organization_id, organization_codefrom org_organization_definitionswhere organization_name like ‘xxx%‘; ( xxx is organization name ) 

Run this script to find wip_entity_id:

select wip_entity_idfrom wip_entitieswhere wip_entity_name = < Wip job or repetitive assembly>and organization_id= ‘xxx‘; ( xxx is organization id ) 

select error_code, error_explanation,lock_flag,inventory_item_id, transaction_action_id,transaction_source_type_id, process_flag,organization_idfrom mtl_material_transactions_tempwhere organization_id =and transaction_source_id = ;( wip_entity_id is obtained from above SQL statement ) 

3. Pending in-process migration Transaction Processing

Wip_move_txn_interface:
Contains the workshop mobile transactions to be processed.

To view the pending mobile transaction processing (navigate to "-> In-process/mobile transaction processing/pending mobile transaction processing ).
Records can be updated, deleted, and resubmitted through forms.

The transaction processing mode should be set to online processing.
If any erroneous mobile transaction is processed in wip_move_txn_interface, the transaction must be resubmitted.

Run the following script:

To learn how many rows are wrong:

select count(*)from wip_move_txn_interfacewhere process_status = 3 ; 

To learn how many rows are to be processed:

select transaction_id, wip_entity_id, process_phase, process_status, wip_entity_namefrom wip_move_txn_interfacewhere process_status in (1,3) 

(Process status = 1 indicates TBD, 3 indicates "error ")

You need to know how many rows are wrong and what their messages are:

select error_message, error_columnfrom wip_txn_interface_errorswhere request_id= ( for the two that have a request id ) 

Use this script to update error records:

update wip_move_txn_interfaceset group_id=null,request_Id = null,process_status=1,transaction_id=nullwhere transaction_id=; 

Restart the interface Manager (mobile and cost manager ).
Start the mobile transaction processing manager, even if TP: In-process mobile transaction processing configuration file option = online processing. (Navigate to "-> Inventory/settings/transaction processing/interface manager ). On the toolbar, press the start manager button.

4. Are you sure you want to process in-process resource transactions?

Perform the following operations to resubmit the error resource cost record;

1. Make sure that no (Resource) Cost worker is running.
2. Stop the (Resource) Cost manager ..
3. Back up the table wip_cost_txn_interface
4. Check the transaction to be processed and run the following SQL statement;

select transaction_id, request_id, group_id,process_status , wip_entity_namefrom wip_cost_txn_interfacewhere process_status in ( 1,3); ( process_status= 1 indicates ‘Pending‘, 3 indicates ‘Error‘ ) 

5. Update the table.

update wip_cost_txn_interfaceset group_id=NULL,transaction_id = NULL,process_status= 1 ( set it to Pending )where process_status = 3; ( error records ) 

6. Restart the (Resource) Cost manager.

5. are discrete tickets to be closed?

Use the apps user to enter SQL * Plus/and run the following SQL script:

wipcljob.sql

Use this script to report discrete tickets within a certain range of an organization, because the transaction to be determined cannot be closed. This is an SQL script that queries the database and generates output to show which transactions cause problems. This script only recognizes data and does not update or modify any data.

This script can also be downloaded here; the SQL script note 106242.1 of the ticket is suspended

Note that no program is running and the manager has been removed when the update script is run.

In order for the work to end smoothly, there must be no transactions that belong to the ticket processed in the table wip_move_txn_interface. If there are, then run the following Script: If yes, run the following script:

Use this script to search for rows to be determined:

select transaction_id, wip_entity_id, process_phase,process_status, wip_entity_namefrom wip_move_txn_interfacewhere process_status in (1,3); (process status = 1 indicates Pending, 3 indicates "Error" )update wip_move_txn_interfaceset group_id=null,process_status=1,transaction_id=nullwhere transaction_id=; 

No transactions that belong to the ticket must be processed in the table wip_cost_txn_interface.
The information contained in wip_cost_txn_interface is the cost of work-in-process.

select transaction_id, wip_entity_id,process_phase, process _statusfrom wip_cost_txn_interfacewhere process_status in (1, 3); 

If there is a transaction processing for this ticket, then run the following script:

update wip_cost_txn_interfaceset group_id=null,process_status=1,transaction_id=nullwhere transaction_id=; 

No transactions that belong to the ticket must be processed in the table mtl_material_transactions_temp.

The mtl_material_transactions_temp table is the key to all raw material transactions.

First, make sure that the inventory Transaction Manager is running.

select transaction_source_id , inventory_item_id, process_flag,error_code, error_explanation,transaction_source_type_id, organization_idfrom mtl_material_transactions_tempwhere process_flag = ?E?and transaction_source_type_id=5; ( 5 = Job/schedule ) 

If any transaction processing for this ticket is in mtl_material_transactions_temp, execute the following script:

update mtl_material_transactions_tempset process_flag = ‘Y‘,lock_flag = ‘N‘,transaction_mode = 3,error_code = NULL,error_explanation = NULLwhere organization_id =and transaction_source_id = ; 

There must be no uncalculated transaction processing for this ticket in the table mtl_material_transactions (where costed_flag in ('n', 'E '))

select request_id, costed_flag, transaction_id,transaction_group_id, inventory_item_id, transaction_source_idfrom mtl_material_transactionswhere costed_flag in (‘N‘, ‘E‘)and transaction_source_type_id=5and organization_id = < org. id > ; 

Update records to resubmit them to the cost manager ..

update mtl_material_transactionsset costed_flag = ‘N‘,request_id = NULL,transaction_group_id = NULL,error_code = NULL,error_explanation = NULLwhere costed_flag in (‘N‘, ‘E‘); 

Restart the cost manager.

If the above conditions are satisfactory, but the problem persists

Change the wip_discrete_jobs status and delete it from wip_dj_close_temp.

1. You need to use sqlplus for updates.
2. Make sure that status_type = 5 indicates that the status in the wip_discrete_jobs table is complete and no fees are charged.
3. You need to delete all corresponding records in the wip_dj_close_temp table.

To find any line of this ticket:

select count (*) from wip_dj_close_tempwhere wip_entity_id = < job‘s wip entity id>and wip_entity_name = < job number > ; 

1. Check entity_type (3 = disabled discretization) in the wip_entities table ).
2. Check status_type (12 = disabled) in wip_discrete_jobs ).

Note:
File: wiprsjob. SQL

This script is used to reset discrete tasks and cannot be closed because of pending transaction processing.

This script can be downloaded here;

Reset discrete work order SQL script note 106245.1

6. uncalculated transactions?

When the inventory close period ends, you may receive a message saying that you cannot close it because uncalculated transactions exist in the specified period. This can happen when the cost management work comes down or is removed at run time, leaving some transactions in the 'state between' with request_id but costed_flag set to 'n '.

The error information of the standard cost is listed in the log file of the cost worker program. However, the error in average cost is in the table (error_code, error_explanation) of the MT itself ).

No uncalculated transactions may be processed in mtl_material_transactions (where costed_flag is in ('n', 'E '))

select request_id, costed_flag, transaction_id,transaction_group_id, inventory_item_id, transaction_source_idfrom mtl_material_transactionswhere costed_flag in (‘N‘, ‘E‘)and transaction_source_type_id=5and organization_id = < org. id > ; 

(If this returns any record-uncalculated transactions exist ).

If the table has uncomputed transaction processing error codes, you can use the provided script.

Important: make sure that you do not have a running cost manager for this outline.

Create a backup table for the record you want to update (always a good practice );

create table mtl_material_txn_bkup as(select * from mtl_material_transactionswhere costed_flag in (‘N‘, ‘E‘); 

Update records are resubmitted to the cost manager.

update mtl_material_transactionsset costed_flag = ‘N‘,request_id = NULL,transaction_group_id = NULL,error_code = NULL,error_explanation = NULLwhere costed_flag in (‘N‘,‘E‘);

Restart the manager to run the cost of this outline.

To handle any product transaction:

Select transaction_id, request_id, group_id, process_status, wip_entity_namefrom wip_cost_txn_interfacewhere process_status in (1, 3); (process_status = 1 indicates 'tb' 3 indicates "error ") (that is, transactions that only exist in the non-computing cost are processed in this table)

Update table:

Update wip_cost_txn_interfaceset request_id = NULL, group_id = NULL, process_status = 1 where process_status = 3; (process_status =? (1 indicates 'tb', 3 indicates "error ")

The following cost manager should select their transaction processing and re-process it. If the transaction processing fails, it is an error condition. Costed_flag = 'E '. Process_status = 1 in the TBD transaction processing of wip_cost_txn_interface. Cost management is run, but these rows have never been selected for processing.

If you are using the average cost, then verify if there is any transaction processing error in mtl_material_transactions. If a transaction error code is there, other transactions in wip_cost_txn_interface will not calculate the cost.

If the average cost is used, all transactions are processed in order. If a transaction fails to be processed, other transactions cannot be processed.

To determine whether an erroneous transaction is processed in mtl_material_transactions, use the following script:

select request_id, costed_flag, transaction_id,transaction_group_id, inventory_item_id, transaction_source_idfrom mtl_material_transactionswhere costed_flag in (‘N‘, ‘E‘)and transaction_source_type_id=5and organization_id = < org. id > ; 

Confirm the error occurred in this transaction, re-process it, and expect it to manage the error information in the cost log file.

Reprocess the transaction:

update mtl_material_transactionsset costed_flag = ‘N‘,request_id = NULL,transaction_group_id = NULL,error_code = NULL,error_explanation = NULLwhere costed_flag in (‘N‘, ‘E‘);COMMIT;

To view the log file of the latest running cost manager, use the responsibilities of the system administrator and query the request name starting with 'cost manager %. Correct the error. Once the error is properly handled, the cost manager starts to process the transaction in wip_cost_txn_interface.

Check whether any tablespace needs to be added and submit it again. A script checks the maximum extent of the invalid object, and the tablespace is as follows;

select owner,object_name, object_typefrom all_objectswhere status = ‘INVALID‘;select segment_name,tablespace_name,extents,max_extentsfrom dba_segmentswhere max_extents >5; 

1. Check the tablespace used by mtl_transaction_accounts.
2. Check whether the original material cost transaction processing workflow (cmcmcw) is running ..

Common scripts for work-in-process and cost troubleshooting