Resolving Period Close Pending Transaction PURPOSE

There are a variety of reasons for pending transactions. The scope of this document is to identify data, and product affecting Period Close process. This information will assist troubleshooting pending transactions that may affect the Period Close process.

VIEWING PENDING TRANSACTIONS Users can see the number of pending transactions by navigating to the Inventory Accounting Periods Form. Navigate > Cost > Accounting Close Cycle > Inventory Accounting Periods Place cursor on the appropriate open accounting period and click on the [Pending] Button. 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, Pending WIP Costing, Uncosted WSM txn and Pending WSM interfaces 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, once the accounting period is closed, unresolved transactions in this zone cannot be processed because they have a transaction date for a closed period. 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.

Resolution Required Transactions: “Unprocessed Material” indicate 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 WIP_COST_TXN_INTERFACE table. “Uncosted WSM txn” indicate there are uncosted transactions in the WSM_SPLIT_MERGE_TRANSACTIONS table. “Pending WSM interface” indicate there are pending transactions in the WSM_SPLIT_MERGE_TXN_INTERFACE table.

Resolution Recommended Transactions: “Pending Receiving” transactions indicate there are unprocessed purchasing transactions in the RCV_TRANSACTIONS_INTERFACE table. These transactions include purchase order receipts and returns for inventory. These transactions are not included in your receiving value if they are not resolved prior to closing the period. “Pending Material” transactions indicate there are unprocessed material transactions in the MTL_TRANSACTIONS_INTERFACE table. “Pending Shop Floor Move” transactions indicate there are unprocessed shop floor move transactions in the WIP_MOVE_TXN_INTERFACE table.

Unprocessed Shipping Transactions: “Pending Transactions” in the Unprocessed Shipping Transactions zone indicate there are transactions in the WSH_DELIVERY_DETAILS table in a status of shipped.

UNPROCESSED MATERIAL TRANSACTIONS: To resolve Unprocessed Material transactions, users need to identify and resolve the errors and issues preventing a record from being processed through the MTL_MATERIAL_TRANSACTIONS_TEMP table. Details of transactions can be viewed through the Applications by navigating to the Pending Transactions form. Inventory > Transactions > Pending Transactions

By using the Pending Transactions window, users view, edit, correct and resubmit unprocessed material transactions. There are five tabs to select the type of information: Error, Location, Source, Intransit and Others, which provide detailed Information on the transactions.

CLEARING UNPROCESSED MATERIAL TRANSACTIONS: Typically following type of errors have been observed for unprocessed material transactions. The steps are also mentioned thereof to clear these errors

Error1 : Failed to pack/unpack item / This LPN is already nested with another LPN

Resolution Steps : a) Please follow the steps in the embedded document to clear these transactions.

Error2 : Inventory_Item_id < 0,Error_code = ‘Transaction processor error’,transaction_uom = Valid UOM or ‘X’

Resolutions Steps :a) Verify the inventory_item_id and error_code in MMTT

SELECT   count(*) FROM mtl_material_transactions_temp MMTT WHERE organization_id = 223and error_code ='Transaction processor error' and inventory_item_id < 0;

b) These are records some fictious records and need to be deleted from MMTT.

Error3 : inventory_item_id > 0,Error_Code = Transaction processor error,transaction_uom = ‘X’,

Resolution Steps : a) Check the related LPN content and ensure LPN is unused. Refer the

attached document.b) If LPN content is null then Resubmit it, it should clear the transaction.c) If the transaction is not cleared, delete it from the MMTT

Error4 : inventory_Item_id > 0,error_code = Transaction processor errortransaction_uom = Valid UOM

Resolutions Steps :a) Resubmit these records from pending transactions window.b) If the transaction does not clear from the MMTT, update the

corresponding records in MMTT with below script

UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP SET PROCESS_FLAG = ‘Y’, LOCK_FLAG = ‘N’, TRANSACTION_MODE = 3, ERROR_CODE = NULL WHERE ORGANIZATION_ID = 223AND ERROR_CODE = ‘Transaction processor error’

Uncosted Material Transactions To resolve uncosted Material Transactions you need to determine and fix what is preventing a record from being processed through the MTL_MATERIAL_TRANSACTIONS table. Details of uncosted transactions can be viewed through the Applications by navigating to the Material Transactions form.Cost > View Transactions > Material TransactionsIn the find Parameter Window there is a field “Costed” and from the list of values “Yes, No, Error or Null” can be selected. The failed transactions are the ones that will need to be corrected and the transactions that are not costed will need to be costed to close the period.

Following reasons are identified for transactions not getting costed.

Reason1 : Transaction Source ID in MMT is populated with value < 0 Resolution : MMT needs to be updated for these uncosted transactions with the appropriate transaction source id. Cost Manager must be shut down before applying this fix.

Following script can be used to update these records

SET SERVEROUTPUT ON SIZE 100000

CREATE TABLE MMT_BKUP_ACR8322AS (SELECT *

FROM mtl_material_transactions WHERE organization_id = 223 AND costed_flag IN ('N', 'E') AND transaction_source_id < 0);

BEGIN --For fixing transaction_source_type_id 4 for SO UPDATE MMT_BKUP_ACR8322 mmt-- MTL_MATERIAL_TRANSACTIONS MMT SET COSTED_FLAG = 'N', TRANSACTION_GROUP_ID = NULL, TRANSACTION_SOURCE_ID = (SELECT DISTINCT header_id FROM mtl_txn_request_lines WHERE LINE_ID = MMT.move_order_line_id AND organization_id = MMT.organization_id AND inventory_item_id = MMT.inventory_item_id), ERROR_CODE = NULL, ERROR_EXPLANATION = NULL WHERE costed_flag IN ('N', 'E') AND transaction_source_type_id = 4 AND transaction_action_id = 2 AND organization_id = 223 AND transaction_source_id < 0 AND EXISTS (SELECT 1 FROM mtl_txn_request_lines WHERE LINE_ID = MMT.move_order_line_id AND organization_id = MMT.organization_id AND inventory_item_id = MMT.inventory_item_id);

COMMIT; DBMS_OUTPUT.PUT_LINE('Rows updated for transaction_source_type_id 4 for SO : ' || sql%ROWCOUNT);

-- ----------------------------------------------------------------------------------- -- For fixing transaction_source_type_id =5 for WIP UPDATE MMT_BKUP_ACR8322 mmt-- MTL_MATERIAL_TRANSACTIONS MMT SET COSTED_FLAG = 'N', TRANSACTION_GROUP_ID = NULL, TRANSACTION_SOURCE_ID = (SELECT wip_entity_id FROM wip_move_transactions wmt

WHERE wmt.transaction_id = mmt.move_transaction_id), ERROR_CODE = NULL, ERROR_EXPLANATION = NULL WHERE costed_flag IN ('N', 'E') AND transaction_source_type_id = 5 AND organization_id = 223 AND transaction_source_id < 0;

COMMIT; DBMS_OUTPUT.PUT_LINE ( 'Rows updated transaction_source_type_id =5 for WIP : ' || sql%ROWCOUNT );-----------------------------------------------------------------------------------EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error ' || SQLERRM);END;/======================

Pending WIP Costing transactions (Resource and Overhead Transactions) Resolving Pending WIP Cost transaction is a process of determining and fixing what is preventing a record form being processed through the WIP_COST_TXN_INTERFACE table. Details of pending transactions can be viewed through the applications by navigating to the pending resource transaction form.

WIP > Resource Transactions > Pending Resource Transactions

Error1 : No charges can be applied to a cancelled/closed job

Resolution : A manual JV need to be passed for such transactions as the job status is closed/cancelled and no charges are allowed on such jobs by Oracle.