Oracle Apps R12 Inventory Month End Issues
We encounter with different issues on Inventory transaction.Here i came up with the some rarely found errors of stuck transaction with the workaround and solutions.
Note: Use this solutions only if they are appropriate to your environments and test it in Non Prod Instances before try to fix in production or raise an oracle SR.
Overview on the Inventory Accounting Period Close
There are a lot of reasons for pending transactions. This Document will guide for few of the repetitive issues and resolving pending transactions preventing an accounting period from being closed.
You can view the number of pending transactions by navigating to the Inventory Accounting Periods form.
Navigate -> Cost ->Accounting Close Cycle ->Inventory Accounting Periods
Put your cursor on the appropriate open accounting period and click on the ‘Pending’. The below screen appears.
There are three zones titled “Resolution Required”, “Resolution Recommended” and “Unprocessed
Shipping Transactions”.
The Resolution Required zone displays the number of Unprocessed material, Uncosted material/WSM, Pending WIP Costing, Pending WSM interface and Pending LCM
Interface transactions existing in this period. Transactions appearing in this zone must be resolved before the period can be closed.
The Resolution Recommended zone displays the number of Pending Receiving, Pending Material and Pending Shop Floor Move transactions existing in this period. Transactions appearing in this zone will not prevent closing of the accounting period. However, these transactions are moved to next period for resolution.
The Unprocessed Shipping Transactions zone displays the number of Pending Transactions existing in this period. Transactions appearing in this zone must be resolved before the period can be closed, depending if the Resolution required or recommended is checked.
The following below issues for the month end stuck transactions were being addressed and fixed with respective data fixes.
1. Pending Transaction Stuck with FOB error and Fix for the duplicate shipment numbers.
2. Uncosted material issue.
3. Orphan item reservations.
4. Fix for the serial numbers that are available but cannot be transacted due mark id populated.
5. Internal order stuck with duplicate lines in the pending Material.
(Issue due to the trips stop of one responsibility picks the delivery of other responsibility)
6. Fix for duplicate inventory transactions.
Pending Transaction Stuck with FOB error and Fix for the duplicate shipment numbers
Issue Description:
The transaction stuck at the receiving side with the error ‘Error:FOB (Free on Board) point is not defined for the organization (91)’.
Replication:
Users were not able to replicate the issue at will but it would occur periodically. Below are example steps that might occur:
Attempt an inter-org transfer (and it usually works).
Sometimes, but not consistently, transactions time-out and fail. (Possibly caused by some network or system bottleneck.)
After the failed transactions, orphan shipment transactions are seen running SQL from Note 789732.1.
If users try to repeat the transaction, it fails with the error.
Root Cause:
· The inventory transaction processor (TrxProcessor.java) inserts inventory transactions (MMT - MTL_MATERIAL_TRANSACTIONS) but before committing calls the receiving manager (RCVTM - RcvTrxManager.java) in online mode to process the receiving interface (RTI) records for Internal Sales Order (ISO) and Inter-Organization Transfer (IOT) intransit shipments. Sometimes, the inventory transactions (MMT) may be rolled back but the receiving manager (RCVTM) is still running (as it is called by fnd_transaction.synchronous), when the connection is lost or a timeout happens. Moreover, the RCVTM will be called for each transaction line in one same header id if the batch id is 0.
· Worked via bug:
BUG 13020024 - CONTINUE 12310375: ROOT CAUSE RVTII-060 MISSING FOB POINT ERROR
Root Cause of such issue is explained in the oracle meta link note id Root Cause RVTII-060 Missing Fob Point Error [Video] (Doc ID 1390812.1).
To fix the root cause of the issue the ‘Patch 13503144:INV.B’ need to applied.
Work around Data Fix:
Two cases are noticed were noticed with issue
Case A:
Some shipment lines have corresponding issue transaction in the source/shipping organization, and some do not. These are considered to be "duplicate" shipment lines.
Case B:
None of the shipment lines have a corresponding issue transaction in the source/shipping organization. These are considered to be "dummy" shipment lines.
Case B.1:
In this case the duplicate shipments get created for the Internal Sales Order. If the original shipment get received .The duplicate get stuck in the Unprocessed materials with the FOB error.
In such scenarios check whether the shipment lines which are stuck are successfully received.
If received successfully follow below steps for data fix from Oracle Meta link Note id 789732.1
· Run the script ‘identifying_duplicate_shipments-new.sql’ to make sure the stuck shipment numbers are duplicate one.
· Run the script ‘delete_dummy_shipment_data-new.sql’. This script clears all the duplicates within the range of the dates provided as a parameter.
· This Script can be run for individual shipment number by including the specific shipment number as a condition in the logic.
Case B.2:
In this case the user tried to transact the same items again with different shipment number.
Let us take an example.
If user has transacted few items using Inter Org transfer with shipment number ‘A’. Due to the locks on the tables as explained in above cause the material transactions are not created, but the shipment lines get created for the ‘A’.
Due to material transactions are not being performed, user tries to re-transact the items. This time the material transactions get created with new shipment number ‘B’.
The user will not able to receive the items on the new shipment number because the serial numbers of the items get locked with the shipment number ‘A’.
The shipment number ‘A’ stuck in the unprocessed material with the FOB error.
In such cases follow the below steps for the data fix,
· Get the serial numbers stuck for the items.
Run the below SQL script to get the exact records has the issue.
select * from MTL_SERIAL_NUMBERS
where Serial_Number in (&serial numbers with comma separated)
and Current_Status=7
and inventory_item_id =&item_id
and Current_Organization_Id=&destination_organization_id ;
· Take back up of the above the output, by creating the backup table.
· Run the update statement
update MTL_SERIAL_NUMBERS
set Current_Status=5,
CURRENT_ORGANIZATION_ID=&Source_Organization_id
where Serial_Number in (&serial numbers with comma separated)
and Current_Status=7
and inventory_item_id =&item_id
and Current_Organization_Id=&Destination_Organization_id
· Run the Script ‘insert_rss_generic.sql’ for the shipment number ‘B’. In order to insert the missing serial transactions which did not created due to locks.
· Now we can clear the duplicate shipment number ‘A’ using the data fix of Case A.
§ Run the script ‘identifying_duplicate_shipments-new.sql’ to make sure the stuck shipment numbers are duplicate one.
§ Run the script ‘delete_dummy_shipment_data-new.sql’. This script clears all the duplicates within the range of the dates provided as a parameter.
§ This Script can be run for individual shipment number by including the specific shipment number as a condition in the logic.
Un costed material issue
Issue Description:
The transactions get stuck with the error ‘CSTPAVCP.Cost_LogicalSOReceipt( 110 ): Error in Insert_Account Transaction type: Internal order issue’ under the un costed material.
Root Cause
In the file CSTSISCB.pls, for a brand new item in standard costing organization, it will have zero item_cost in cic(cst_item_costs) but no record in cicd(cst_item_cost_details).
This is explained in the following bug:
Bug 9194185 - ROOT CAUSE (SEE BUG 9166534) CSTPAVCP.COST_LOGICALSORECEIPT( 110 )
Items have 0.00 item cost in the Frozen Cost type in the CST_Item_Costs (CIC) table and no costs
in CST_Item_Cost_Details (CICD) for the frozen cost type for any of these items.
Bug 5902784, error CSTPAVCP.Cost_LogicalSOReceipt( 110 ): Error in Insert_Account will be received if there are no costs in the CIC or CICD tables for the items in error.
To fix the root cause
1. Download and review the readme and pre-requisites for Patch.9194185.
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Confirm the following file versions:
CSTSISCB.pls 115.16.115100.4
you can use the commands like the following:
strings -a $BOM_TOP/CSTSISCB.pls |grep '$Header'
5. Retest the issue.
6. Migrate the solution as appropriate to other environments.
Work around Data Fix:
Follow the below steps for the data fix.
· Determine if there are costs in the following tables in the Frozen cost type for the items in question:
SELECT * FROM cst_item_costs
WHERE organization_id =&orgid
AND inventory_item_id =&item_id;
SELECT * FROM cst_item_cost_details
WHERE organization_id =&orgid
AND inventory_item_id = &item_id;
· Define costs for these items, in the related organizations, against 'Pending' or any
other user defined cost type using the Item Costs Form.
· Then run a Standard cost update for these items from Pending (or any other user
defined cost type) so that cost details get created for the items against FROZEN cost type.
· Then, verify that item costs exists for these items using the Item Costs Form.
· Create a back up table
Create table MMT as select * from mtl_material_transactions
where costed_flag in ('E','N')
· Cancel the Cost Manager
System Administration--- Requests - View - Go to Specific Request and in the name give Cost Manager. From there cancel the cost manger that is pending scheduled..
· Re-submission.
Update Wip_cost_txn_interface
set group_id='N',
TRANSACTION_ID=NULL,
REQUEST_ID=NULL,
PROCESS_STATUS=1
WHERE PROCESS_STATUS=3;
Update mtl_material_transactions
set costed_flag='N',
transaction_group_id=null,
error_code=null,
error_explanation=null
where costed_flag in ('E','N');
Commit;
Orphan item reservations
Issue Description:
In Oracle Inventory Management, you may be experiencing some or all of the following symptoms:
Demand is incorrect for closed sales order lines
Reservations exist for sales order lines that have shipped
Demand exists for sales order lines that have shipped
Supply/Demand Screen shows records for a sales order, but the sales order is closed.
Steps to Reproduce in Oracle Inventory Management:
1. In Order Management Super user navigate to Orders, Returns > Order Organizer.
2. Find a sales order with closed sales order lines (that were demanded or reserved).
How to tell if the Sales Order is reserved or demanded
1. In Inventory Responsibility navigate to > On-Hand Availability > Supply/Demand
2. Select Organization.
3. Query the item.
4. Select All Subinventory Locations.
5. A detail row reveals that the item is reserved for a sales order and quantity (shown as a negative number).
6. Expected results: The demand/reservation should not appear.
Root Cause
The cause of this issue is due to code in INVRSV3B.pls and INVVRVAB.pls.
You could use a command like the following to check your versions
grep '$Header' $INV_TOP/patch/115/sql/INVRSV3B.pls
grep '$Header' $INV_TOP/patch/115/sql/INVVRVAB.pls
strings -a $INV_TOP/patch/115/sql/INVRSV3B.pls | grep '$Header'
To avoid the issue in the future, below patch need to applied.
R12.1.x
Patch 9252341:R12.INV.B
INVVRVAB.pls
120.12.12010000.6
Work Around Data Fix:
· Run the script ‘i2471362.sql’, the cleanup script i2471362.sql comes standard in the installation under $INV_TOP/patch/115/sql.
· This script will remove outstanding reservation records that exist for order lines which are closed or cancelled. It does this for both external sales orders and internal orders.
Also, the script cleans reservations left after the Order line is deleted but reservations exist.
The script deletes records from MTL_DEMAND which are not in sync with
MTL_RESERVATIONS.
Fix for the serial numbers that are available but cannot be transacted due mark id populated
Issue Description:
Try to transact a serialized item that has a quantity onhand using the Miscellaneous Transaction Form but cannot see a serial number available.
The list of values for the serial number contains no entries.
The following error appears when the user types in the serial number.
Error
FRM-40212: Invalid value for field FM_SERIAL_NUMBER
-OR-
APP-INV-05577: The serial numbers are not available for use in this transaction
The root cause of this issue is depends on the type of the issue on the serial number. There are many patches available as part of the fix.
We need to log an oracle SR to get the right patch when we have issue replicated.
Workaround Data Fix:
Unable To Do Miscellaneous Transaction On Serial Controlled Item (Doc ID 144875.1)
Note: Before running the below update statement check whether the Serial number is not being used by the active or open transactions.
Update mtl_serial_numbers
Set group_mark_id = NULL
, Line_mark_id = NULL
, Lot_line_mark_id = NULL
where inventory_item_id = &itemid
and serial_number = &serialnum;
Commit.
Internal order stuck with duplicate lines in the pending Material
Issue Description:Sometimes the material transactions related to the Internal Order get stuck in the Pending material with the duplicate lines i.e the two material transaction lines get created for single line id of sales order.
Root cause:
Root cause of this kind of issue is not exactly known, but the analysis is going on the ‘Interface tripstop program’ which is picking up the deliveries from other operating unit to process.
For example: The scheduled program ‘Interface trips stop’ from DK processing the deliveries of the US operating unit.
In this scenario the transactions are getting stuck with the invalid distribution account error i.e the security rule of the one operating unit is validating the account of other operating unit which results in error.
Workaround DataFix:
Note: Test the fix in test instance first and make sure the duplicates exists.
Make sure internal order in the shipping transaction form is with status of next step as ‘Run Interfaces’.
· We need to delete the transactions stuck under Pending Material which are duplicates for the particular internal order.
· Then resubmit again the ‘Interface Trips Stop SRS’ program from order management from the respective operating unit.
Fix for duplicate inventory transactions
Issue Description:
The count of the inventory duplicate transactions shows up in the ‘Unprocessed material’, but we cannot query those records from the ‘Inventory Accounting Periods’.
Root Cause:
While processing transactions through open interface, if batch id is populated and if the first row in a batch fails then the previous batch, which got successfully processed was not getting deleted from pending transactions table and open interface table.
We need to log SR to get the exact patch for the Oracle R12.1.2,currently there are patches shown for R12.0.X from the metalinknote id
Inventory Standard Datafix Instruction #17: Fixing Duplicate Sales Order Transactions In MTI, MMTT and MMT (Doc ID 1472074.1)
Workaround Datafix:
Please apply the note solution step by step.
· Login to the SQL*Plus in bolinf schema
· Place the files INV17-ident.sql, INV17-backup.sql and INV17-datafix.sql in D drive.
· Run the Script INV17-ident.sql as
@D:\INV17-ident.sql
· Then run the Script INV17-backup to take the backup
@D:\INV17-ident.sql
· Login to the SQL*Plus in apps schema
· After taking the backup now run the script INV17-datafix.sql as
@D:\ INV17-datafix.sql
· These scripts commit by themselves.
· Then, after applying the datafix scripts please provide:
select count(*)
from mtl_material_transactions_temp
where transaction_mode = 8
If you receive any row then please follow the steps below:
1. Run the following queries:
a.
select count(*) 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.picking_line_id is not null ;
b.
select count(*) 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.picking_line_id is not null ;
c.
select count(*) 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.picking_line_id is not null;
You should make sure ALL THREE QUERIES RETURN 0 COUNT..
2. If it returns zero rows then execute:
select count(*)
from mtl_material_transactions_temp
where transaction_mode = 8;
If it returns count>0 go to step 3, otherwise the data is fixed and the period could be closed.
3. You are executing this step ONLY if query 2º returned rows:
Update MTL_MATERIAL_TRANSACTIONS_TEMP
Set PROCESS_FLAG = ‘Y’,
LOCK_FLAG = ‘N’,
TRANSACTION_MODE = 3,
ERROR_CODE = NULL
Where TRANSACTION_MODE = 8;
Commit;
4. Launch the Transactions Manager to process the pending transactions and confirm the worker finishes fine.
No comments:
Post a Comment