Manufacturing Transaction Diagnostics－ FUNCTIONAL USER SQL INVESTIGATION and REPAIR

Goal

This document was produced for an Oracle Premier customer that wanted a consolidated list of investigative / repair sql addressing manufacturing processes. The idea was to include DEV specific suggestions not published in Metalink (MyOracle Support) as well as known solutions in one doc to save time. The commentary is derived from DEV, myself and Global Support.

The following SQL is meant to assist in debugging transactional issues and in some cases repair such transactions. This list is not exhaustive and not meant to address all transactional errors. In some cases, Oracle development is reluctant to publish update SQL statements without having reviewed basic data. The following ‘select’ SQL statements are meant to be used to supply such data to Oracle development if so required OR enable customers to facilitate the repair on site.

[@more@]

Solution

==========
DISCLAIMER
==========
Of course, Oracle does not endorse the use of the following document UNLESS directed
by Oracle Global Support or Oracle Development.

=====
USAGE
=====
* Always be cautious, taking backups or using TEST instance before implementing in production.

* Using Find or CTRL F to navigate through this doc is the best way that I have found to make
make my search easy and fast.

Conclusion
==========
At the end of this document you will find a list of transactions that are pending review
and recommendation. This list is not exhaustive and will change.

QUESTIONS? Contact
===============================================

TOPICS COVERED IN THIS DOCUMENT
===============================

FUNCTIONAL USER SQL INVESTIGATION and REPAIR

Best Practices Environment Initiation
DUPLICATE TRANSACTIONS SCRIPT
NEGATIVE BALANCES/AVAILABILITY
SERIAL NUMBERS SCRIPT
INVENTORY LOCATORS SCRIPT
INTERFACE TRIP STOP(ITS) TRANSACTION ERRORS

MONTH END CLOSING as VIEWED FROM THE INVENTORY ACCOUNTING PERIOD->Pending Transactions
- The key steps to resolving pending transactions are
* Typical SQL that can be ran proactively:
Resolution Required Transactions:
UNPROCESSED MATERIAL TRANSACTIONS
PENDING MATERIAL TRANSACTIONS
UNCOSTED MATERIAL TRANSACTIONS EXIST for this PERIOD
PENDING WIP COSTING TRANSACTIONS EXIST in this PERIOD
PENDING RESOURCE TRANSACTIONS
PENDING MOVE TRANSACTIONS for this PERIOD
WIP Month End Uncosted Transactions mtl_material_transactions
RETURN MATERIAL AUTHORIZATIONS
* RECEIVING TRANSACTIONS
Uncosted WSM Transaction
Pending WSM Interface

Workflow – PO Processes
SPECIAL CHARACTERS During ITEM IMPORT PROCESSES
- PO INVESTIGATION SQL
- For PO in WF
- PO as related to MTL_SUPPLY
- GENERAL PO INVESTIGATION
- Autocreate Purchase Orders from Requistions WF Investigation
- Currency mismatch in blanket PO
MFG – mtl_supply table mismatch
SHIPPING TRANSACTION – WF (extent issues)
OM WFt.
Inventory Transaction Interface – Internal Sales Orders Stuck
MRP_SALES_ORDER_UPDATES
SALES ORDER DEMAND NOT PROCESSED MRP_RELIEF_INTERFACE
AP Distribution Related Errors Prevent Closing
Sourcing Rules Investigation

END TOPICS COVERED IN THIS DOCUMENT
===================================

Best Practices Environment Initiation
=====================================
set the org context using the following statement.
exec dbms_application_info.set_client_info(&operating_unit_id);

How to find the ORGANIZATION_ID run this script:
select organization_id, organization_code
from org_organization_definitions
where organization_name like 'xxx%'; ( xxx is organization name )

DUPLICATE TRANSACTIONS SCRIPT
-----------------------------
These scripts identify duplicates records in the transaction tables:

create or replace view mmt_mti_records_v as
select a.transaction_interface_id,
a.picking_line_id
from mtl_material_transactions b,
mtl_transactions_interface a
where a.picking_line_id = b.picking_line_id
and a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.transaction_quantity <0
and b.picking_line_id is not null;

create or replace view mmtt_mti_records_v as
select a.transaction_interface_id,
a.picking_line_id
from mtl_material_transactions_temp b,
mtl_transactions_interface a
where a.picking_line_id = b.picking_line_id
and a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.transaction_quantity <0
and b.picking_line_id is not null ;

create or replace view mmt_mmtt_records_v as
select a.transaction_temp_id,
a.picking_line_id
from mtl_material_transactions b,
mtl_material_transactions_temp a
where a.picking_line_id = b.picking_line_id
and a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in ( 2,8)
and b.transaction_quantity <0
and b.picking_line_id is not null;

Now provide the output of the following:

select count(*)
from mmt_mti_records_v;

select count(*)
from mmtt_mti_records_v;

select count(*)
from mmt_mmtt_records_v;

NEGATIVE BALANCES/AVAILABILITY
------------------------------
Please run the following script to check for the correct on hand quantity
for the item. This procedure works on 11i or higher. (The package used
does not exist in 11.0.3 or 10.7)

set serveroutput on
prompt Enter Organization_id
accept org_id
prompt Enter Inventory_item_id
accept item_id
prompt Enter Subinventory Code
accept subinventory

DECLARE
L_api_return_status VARCHAR2(1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
BEGIN inv_quantity_tree_grp.clear_quantity_cache;
dbms_output.put_line('Transaction Mode');
apps.INV_Quantity_Tree_PUB.Query_Quantities (
p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => L_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => &org_id,
p_inventory_item_id => &item_id,
p_tree_mode => apps.INV_Quantity_Tree_PUB.g_transaction_mode,
p_onhand_source => NULL,
p_is_revision_control=> false,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE, p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => '&Subinventory',
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr ); dbms_output.put_line('Quantity on hand'||to_char(l_qty_oh));
dbms_output.put_line('Quantity res oh'||to_char(l_qty_res_oh));
dbms_output.put_line('Quantity res '||to_char(l_qty_res));
dbms_output.put_line('Quantity sug '||to_char(l_qty_sug));
dbms_output.put_line('Quantity ATT'||to_char(l_qty_att));
dbms_output.put_line('Quantity ATR'||to_char(l_qty_atr));
end; /

SERIAL NUMBERS SCRIPT
---------------------
1) To identify Serial Number Information such as status and location:
select substr(msn.serial_number,1,15) "Serial Num",
substr(msn.current_status,1,6) "Status",
substr(decode(msn.group_mark_id,NULL, 'No Value',
msn.group_mark_id),1,8) "Mark Id",
substr(decode(msn.line_mark_id,NULL, 'No Value',msn.line_mark_id),1,8) "Ln Id",
substr(decode(
msn.lot_line_mark_id,NULL,'No Value',msn.lot_line_mark_id),1,12) "Lot Mark Id",
substr(msn.inventory_item_id,1,9) "Item Id",
substr(msn.current_organization_id,1,9) "Org Id",
substr(msn.current_subinventory_code,1,15) "Current Subinv",
substr(decode(
msn.current_locator_id,NULL, 'No Loc',msn.current_locator_id),1,9) "Loc Id"
from mtl_serial_numbers msn
where serial_number = '&Serial_Number';

2) Run this script to identify serial number transactions history in both tables:
MTL_MATERIAL_TRANSACTIONS (Transaction Historical) and
MTL_UNIT_TRANSACTIONS (Serialized/Lot Transaction Historical).

select substr(mmt.transaction_id,1,15) "Txn Id",
substr(mut.transaction_id,1,15) "Txn Id2",
substr(mut.serial_number,1,15) "Serial",
substr(mmt.transaction_date,1,15) "Txn Date",
substr(mut.transaction_date,1,15) "Txn Date2",
substr(mmt.inventory_item_id,1,15) "Item Id",
substr(mmt.organization_id,1,10) "Org",
substr(mmt.subinventory_code,1,20) "Subinv",
substr(mmt.transaction_type_id,1,15) "Txn Type",
substr(mmt.transaction_quantity,1,15) "Txn Qty",
substr(mmt.primary_quantity,1,15) "Pri Qty"
from mtl_material_transactions mmt,
mtl_unit_transactions mut
where mmt.transaction_id = mut.transaction_id
and mut.serial_number = '&Serial_number'
and mmt.organization_id = mut.organization_id
and mmt.inventory_item_id = mut.inventory_item_id;

INVENTORY LOCATORS SCRIPT
-------------------------
Locators Script: These scripts are used to identify Locator information
before datafixes.

1) Identify locator and item number.
select msl.inventory_item_id ITEM_ID,
msl.organization_id ORG_ID,
msl.secondary_locator LOCATOR_ID,
msl.subinventory_code SUBINVENTORY
FROM mtl_secondary_locators msl,
mtl_item_flexfields mif
WHERE mif.item_number = ‘&item number’
AND mif.inventory_item_id = msl.inventory_item_id
AND mif.organization_id = msl.organization_id
AND mif.organization_id = &Org_id;

2) select inventory_location_id LOCATOR_ID,
organization_id ORG_ID,
subinventory_code SUBINVENTORY,
segment1 || '+' || segment2 || '+' || segment3 || '+' || segment4 LOCATOR
FROM mtl_item_locations
WHERE inventory_location_id in (, , …)

SCRIPT #3&4, below, have Patchset G considerations:

For Patchset lower than INV G:
3) select substr(create_transaction_id,1,8) "Create Txn Id",
substr(update_transaction_id,1,8) "UpTxn Id",
substr(locator_id,1,8) "Loc Id",
substr(transaction_quantity,1,8) "Txn Qty",
substr(inventory_item_id,1,8) "Item Id",
substr(organization_id,1,8) "Org Id",
substr(subinventory_code,1,15) "Subinv"
from mtl_onhand_quantities
where inventory_item_id = &item_id
and organization_id = &org_id
and subinventory_code = '&subinv';

For Patchset INV G or higher:
4) select substr(create_transaction_id,1,8) "Create Txn Id",
substr(update_transaction_id,1,8) "UpTxn Id",
substr(locator_id,1,8) "Loc Id",
substr(transaction_quantity,1,8) "Txn Qty",
substr(inventory_item_id,1,8) "Item Id",
substr(organization_id,1,8) "Org Id",
substr(subinventory_code,1,15) "Subinv"
from mtl_onhand_quantities_detail
where inventory_item_id = &item_id
and organization_id = &org_id
and subinventory_code = '&subinv';

5) select substr(transaction_id,1,8) "Txn Id",
substr(transaction_type_id,1,8) "Txn Type",
substr(transaction_quantity,1,8) "Txn Qty",
substr(primary_quantity,1,8) "Prim Qty",
substr(inventory_item_id,1,8) "Item Id",
substr(organization_id,1,9) "Org Id",
substr(subinventory_code,1,15) "Subinv",
substr(locator_id,1,9) "Loc Id"
from mtl_material_transactions
where transaction_id = &txn_id ;

6) select substr(transaction_id,1,9) "Txn Id",
substr(inventory_item_id,1,9) "Item Id",
substr(organization_id,1,9) "Org Id",
substr(subinventory_code,1,15) "Subinv",
substr(locator_id,1,9) "Loc Id",
substr(transaction_date,1,12) "Txn Date"
from mtl_unit_transactions where transaction_id = ;

INTERFACE TRIP STOP(ITS) TRANSACTION ERRORS
-------------------------------------------
- To obtain detailed information regarding unprocessed Shipping Transaction Navigation:
Order Management > Shipping > Interfaces; Run, Single Request "Interface Trip Stop-SRS” program.

Submit the program with parameters below:
– Mode: All
- Delivery: get from LOV
- Log Level : 5

The following are the some of the frequent, generic types of data corruption issues
that occur during the month end process.
-----------------------------------------------------------------------------------

1) Error in ITS LOG : The customer is still getting the following workflow error:
ORA-20002: 3100: Item 'OEOL/538957' does not exist. in Package
OE_ORDER_WF_UTIL Procedure Create_LineFork
Error msg: ORA-20002: 3100: Item 'OEOL/538957' does not exist. in Package
OE_ORDER_WF_UTIL Procedure Create_LineFork'.

Cause : The root cause of the failure in trip stop is that, the service(warranty)
line attached to this Shippable line has been closed (before upgrade) and no WF information
available. Trip stop is failing to find the WF info for the service line.

Sample Solution :

update oe_order_lines_all
set model_remnant_flag='Y',
line_set_id=null,
service_reference_line_id=null
where line_Id in (538956,538957,538962,538963);

commit;
exit;

After the above update the ITS has to be rerun so to process the Orderline
further.

2) Error in ITS LOG : InterfaceTripStop: result of INVENTORY interfacing stop_id 413210 =
ERROR: unknown status = ''InterfaceTripStop: Stopping because of ERROR.

Sample Solution :

UPDATE WSH_DELIVERY_DETAILS
SET transaction_temp_id = null
WHERE delivery_detail_id = 960210322;

Commit;

Subsequently run Interface Trip Stop.

3) Also we might encounter cases where Unprocessed Shipping Transactions
not allowing month end close .The cause here is due to Inventory Interface
flag is 'N' where as the other processes(OM and Receivables interfaces have
gone through correctly. The fix would be to reset the process_flag in
WSH_TRIP_STOPS table so that ITS will pick it up again. If this does not
work then we may need to do Miscellaneous Issue(make sure COGS account is same),
set Inventory Interface flag to 'Y' for the problemmatic line after obtaining
customer approval.

4) To relieve reservations after Order Shipments there is a standard script (i2471362.sql)
available from patch: <<2471362>>.

5) You can request/search for the script, Cancelorderline1.sql. Use cancel the order line for
which we need to pass the Order Line Id as the parameter. This script helps you to cancel
the order line when User wishes to cancel order lines and encounter errors.

6) To discover stuck deliveries:

The navigation to find out the stuck deliveries:
Shipping>Interfaces>Run>Interface Trip Stop - SRS.

In this program there is a parameter for Delivery Id. The LOV for this retrieves all
deliveries(stuck as well as delivery which has not yet been picked up). As this program
is running at scheduled intervals it is most likely that only stuck records are in the LOV.
User would need to find out whether the Delivery belongs to their sales order by querying
on the delivery from shipping transactions form.

To get the stuck deliveries in the ITS log, set the OM debug to 5 and run ITS and the log
will contain all the stuck delivery numbers. However , depending on the nature of the problem
reported please check the data from back end before suggesting any scripts.

MONTH END CLOSING as VIEWED FROM THE INVENTORY ACCOUNTING PERIOD->Pending Transactions
If there are errors, sometimes the transaction can be cleared by simply re-submitting it.
Select the lines to be re-submitted. Or go to the tools menu and select all. Lines selected
for re-submission will show up in blue and the submit box will be checked. Save the record
this will re-submit the items.

NOTE: LINES WILL NOT BE RE-SUBMITTED UNTIL THE RECORD IS SAVED!

The key steps to resolving pending transactions are:
- Locate the transactions
- Find the error message to determine what is preventing the transactions from processing.
- Resolve the error
- Resubmit the pending record.

Typical SQL that can be ran proactively:

MTL_MATERIAL_TRANSACTIONS_TEMP
------------------------------
- INCTCM is the Transaction Manager for this Table
select count(*) from mtl_material_transactions_temp
where acct_period_id = &acct_period_id;

MTL_MATERIAL_TRANSACTIONS
-------------------------
- CMCTCM is the Cost Manager for the records to be costed in this table.
- CMCCCM is the Cost Collection Manager for the records to be imported to Project Mfg.
select count(*) from mtl_material_transactions
where costed_flag in ('N','E') and acct_period_id = &acct_period_id;

MTL_TRANSACTIONS_INTERFACE
--------------------------
- INCTCM is the Transaction Manager for this Table.
select count(*) from mtl_transactions_interface
where acct_period_id = &acct_period_id;

WIP_COST_TXN_INTERFACE
----------------------
- Resource Cost Worker processes records in this table.
select count(*) from WIP_COST_TXN_INTERFACE
where acct_period_id = &acct_period_id;

WIP_MOVE_TXN_INTERFACE
----------------------
- Wip Move Transaction Worker processes records in this table (WICTCM)
select count(*) from WIP_MOVE_TXN_INTERFACE
where acct_period_id = &acct_period_id;

Resolution Required Transactions
--------------------------------
- Unprocessed Material” indicates that there are unprocessed material transactions
in the MTL_MATERIAL_TRANSACTONS_TEMP table.
- Uncosted Material” indicate there are material transactions in the MTL_MATERIAL_TRANSACTIONS
table with unprocessed accounting entries.
- Pending WIP Costing” transactions indicate there are unprocessed resource and overhead
accounting transactions in the

UNPROCESSED MATERIAL TRANSACTIONS
---------------------------------
Unprocessed material transactions exist for this period. This message indicates you have
unprocessed material transactions in the MTL_MATERIAL_TRANSACTIONS_TEMP table. You are unable
to close the period with this condition. Please see your system administrator. Inventory considers
entries in this table as part of the quantity movement. Closing the period in this situation
is not allowed because the resultant accounting entries would have a transaction date for a
closed period, and never be picked up by the period close or general ledger transfer process.

- Is the Cost Manager down?
- If the Costed = Error, then the transaction has failed costing

If there are rows, as verified by Pending Transaction screen:
- How to find the WIP_Entity_Id run this script:
select wip_entity_id
from wip_entities
where wip_entity_name = < Wip job or repetitive assembly>
and organization_id= 'xxx'; ( xxx is organization id )

select transaction_header_id,
organization_id,
transaction_date,
transaction_mode,
error_code
from mtl_material_transactions_temp
where error_code is not null
order by transaction_date;

Fix the problem for the transaction_header_id with error code specified.

Resubmit the records via the following SQL statement:
Update TL_MATERIAL_TRANSACTIONS_TEMP
Set POCESS_FLAG = 'Y',
LOCK_FLAG = 'N',
RANSACTION_MODE = 3,
ERROR_CODE = NULL
where TRANSACTION_HEADER_ID = '&TRANSACTION_HEADER_ID';

If there are Pending Transactions that are stuck in the MTL_MATERIAL_TRANSACTIONS_TEMP table
with transaction_type_id=5 (backflush/wip transactions) with proces_flag =E, they need to
be submitted.
--------------------------------------------------------------------------------------------
To investigate why the Transactions are Failing, run the following SQL Script:
select transaction_source_id,
inventory_item_id,
process_flag,
error_code,
error_explanation,
transaction_source_type_id,
organization_id
from mtl_material_transactions_temp
where process_flag = ‘E’
and transaction_source_id= ;

To resubmit the transactions use this script:
Update mtl_material_transactions_temp
set process_flag = 'Y',
lock_flag = 'N',
transaction_mode= 3,
error_code = NULL,
error_explaination = NULL
where process_flag = 'E'
and transaction_source_id= ;

PENDING MATERIAL TRANSACTIONS
-----------------------------
- The erred records can be resubmitted via the following SQL statement:

Update MTL_TRANSACTIONS_INTERFACE
Set PROCESS_FLAG = 1,
LOCK_FLAG = 2,
TRANSACTION_MODE = 3,
ERROR_CODE = NULL
Where PROCESS_FLAG = 3;

- The “Process transaction interface” (short name INCTCM) is the manager that will launch
the “Inventory transaction worker” (short name INCTCW) to process the transactions in the
MTL_TRANSACTIONS_INTERFACE table. If the error message in the Transactions Interface window
is not clear reviewing the log file of the “Inventory transactions Worker” may provide more
information that could help in resolving the error.

UNCOSTED MATERIAL TRANSACTIONS EXIST for this PERIOD
----------------------------------------------------
- This message indicates you have material transactions in the MTL_MATERIAL_TRANSACTIONS
table with no accounting entries (Standard Costing) and no accounting entries and no costs
(Average Costing). You are unable to close the period with this condition. These transactions
are part of your inventory value. Closing the period in this situation is not allowed because
the resultant accounting entries would have a transaction date for a closed period, and never
be picked up by the period close or general ledger transfer process. Generally these
are Resource transactions which cannot be processed

Return the number of MMT rows with costed_flag = 'E'

select count(*) from mtl_material_transactions where costed_flag = 'E';

OR use the following to identify these rows by date:

select count(*),
transaction_date
from mtl_material_transactions
where costed_flag = 'E'
group by transaction_date;

OR use the following:

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;

To Return the number of MMT rows with costed_flag = 'E' and no error message:
select count(*)
from mtl_material_transactions
where costed_flag = 'E'
and error_explanation is null;

To Determine the number and type of MMT cost errored transactions:

select count(*),
transaction_type_id TxnTyp,
transaction_action_id TxnAct,
transaction_source_type_id TxnSrsTyp
from mtl_material_transactions
where costed_flag = 'E'
and error_explanation is null
group by transaction_type_id, transaction_action_id,
transaction_source_type_id;

At this point check the "Material costs transaction worker".
If unable to locate the worker with the error info you should attempt to
resubmit the rows via SQL Plus as follows:

!FIRST SHUT DOWN THE COST MANAGER!

update mtl_material_transactions
set costed_flag = 'N',
transaction_group_id = null
where costed_flag is not null;

commit;

Then locate the latest request for the worker. The correct worker request should be the
one that completes with warning. This worker will contain the error reason for each failed
transaction by transaction id #.

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 (check for latest SQL in Metalink)
- Repetitive Jobs cm325557.sql (check for latest SQL in Metalink)

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 WIP COSTING TRANSACTIO