Resolving Period Close Inventory Pending Transaction Pending Transactions Guide 6 of 32 Pending Transactions Guide 14 of 32
PURPOSE
There are a variety of reasons for pending transactions. The scope of this paper 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. Pending Transactions Guide 1 of 32
Resolution Required Transactions:
"
MTL_MATERIAL_TRANSACTONS_TEMP table.
"
MTL_MATERIAL_TRANSACTIONS table with unprocessed accounting entries.
Unprocessed Material" indicate there are unprocessed material transactions in the Uncosted Material" indicate there are material transactions in the
"Pending WIP Costing"
transactions indicate there are unprocessed resource and overhead accounting transactions in the WIP_COST_TXN_INTERFACE table.
"Uncosted WSM txn"
WSM_SPLIT_MERGE_TRANSACTIONS table.
indicate there are uncosted transactions in the
"Pending WSM interface"
WSM_SPLIT_MERGE_TXN_INTERFACE table.
indicate there are pending transactions in the
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. Pending Transactions Guide 2 of 32
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.
The Main Descriptive fields give detailed information on the type of transaction:
Primary UOM
, Transaction quantity in terms of the primary uom of the item
Qty,
Quantity of this transaction
Date,
Transaction date
Item Description,
Inventory item description
Error Explanation,
detailed explanation of error
Lot/Serial
(Used if transaction is associated with a Lot or Serial number)
Submit,
When checked and saved the erred transaction line will be resubmitted. If the line is not in error, and it cannot be resubmitted using the Submit check box but will have to be resubmitted through a SQL*Plus script. All failed transactions can be resubmitted by clicking on ‘Tools’ resubmit all.
Fields Common to all Tabs:
Item,
Inventory item referenced by the line item. Pending Transactions Guide 3 of 32
Transaction Qty,
Quantity of this transaction.
Transaction UOM,
Unit of measure used in the transaction.
Transaction Type,
Displays the transaction type.
Fields in Error Tab region are as follows:
Overcompletion Transactions,
Overcompletion quantity
Transaction Date,
Date the transaction was entered for processing.
Transaction Header Id,
Line item’s header ID used to group transactions in concurrent manager.
Transaction Temp Id,
Transaction line identifier used to join to lot or serial table when under these controls
Transaction Mode,
Method used to process the line item, such as concurrent processing.
Process flag,
Concurrent manager. The process flag codes are:
1 = Pending
2 = Running
3 = Error
indicates whether the row has been processed by the
Transaction Status,
Indicates if it has to be processed or just saved. NULL or 1 is default behavior. 2 is save only. 3 is ready to process
Error Code,
Code describing the error on the last attempt to process the line item.
Error Explanation,
Full explanation of the error that occurred when attempting to process the line item
Fields in Location Tab region are as follows:
Revision,
Revision of the inventory item referenced by the line item.
Org Code,
Organization code of the organization referenced by the line item.
Subinventory,
Subinventory referenced by the line item.
Locator,
Locator referenced by the line item.
Transfer Subinv,
Destination subinventory.
Transfer Org Code,
Organization code of the destination organization.
Transfer Location,
Destination location.
Owning Org,
Owning organization identifier
Planning Org,
Planning organization identifier
Supplier,
supplier name
Cartonized LPN,
Sequential LPN Id generated by cartonization - used by WMS only
Lot Number,
Lot number
Lot Expiration Date,
Lot expiration date
Serial Number,
Serial Number
Fields in Source Tab region are as follows:
Transaction Action,
The transaction action
Transaction Source Type,
Source type of the transaction, such as WIP Job or Schedule
Source,
Source of the transaction, such as account number
Distribution Account,
Distribution account for the line item
Transaction Cost,
Cost to process the transaction
Transaction Reference,
Reference text describing the transaction Pending Transactions Guide 4 of 32
Reason Name,
The transaction reason.
Fields in the Intransit Tab region are as follows:
Shipment Number,
Shipment number for the line item
Transfer Cost,
Cost to process the transfer
Transportation Cost,
Cost to physically transfer the material, such as freight carrier charges
Transportation Account,
General ledger distribution account that collects the costs associated with using the freight carrier for the transfer
Freight Code,
Freight carrier for the transaction
Containers,
Number of containers in which the material is stored for the transfer
Waybill Airbill,
The waybill or airbill number for the transfer
Expected Arrival Date,
Date you expect to receive the material at the destination organization
Fields in the Others Tab region are as follows:
Line Code,
Request ID assigned by the concurrent manager to the line item
Employee Code,
Employee who entered the transaction
Operation Seq Num,
Number associated with the job or schedule referenced by the line item
Department Code,
Oracle Work in Process department code for the line item
Transaction Source Line ID,
Oracle Work in Process line description for the item
Transaction Source Delivery ID,
Line item detail identifier of the demand source
Encumbrance Amount,
Amount encumbered, reserved against funds, when the purchase order or requisition was approved
New Average Cost,
Recalculated average unit cost for the item
Cost Group ID,
Cost group identifier
Value Change,
Amount used to increment the current inventory value.
Percentage Change,
Percentage used to update the item cost.
Location Code,
Ship–to location
Source Project Number,
The source project number
Source Task Number,
The source task number
Project Number,
The project number
Task Number,
The task number
To Project Number,
The to project number
To Task Number,
The to task number
Expenditure Type,
The expenditure type
Expenditure Org,
The expenditure organization
Resubmitting Unprocessed Material Transactions:
Before re-submitting records through the Pending Transactions Form, you can gather information regarding your data and the Inventory Transaction Managers and workers.
Pending Transactions Guide 5 of 32
The following Diagnostic Scripts should be used to identify data, errors, and status of concurrent and interface managers/workers.
Inventory Period Close Diagnostic Test - for the period in question
(INVPeriodClose115.sql) Note: 206576.1
Oracle Inventory Support Svc Req Instrumentation for Stuck Transactions MMTT
(INVMMTT115h.sql) Note: 204577.1
In the Pending Transactions window either select the Resubmit check box to resubmit one record or choose Select All to Resubmit from the Special Menu and selectively deselect individual transaction you do not want to resubmit, save your work. Transactions can also be resubmitted via the following SQL statement:
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_MATERIAL_TRANSACTIONS_TEMP table. If the error message in the Pending Transactions window is not clear reviewing the log file of the "Inventory transactions Worker" may provide more information that could help in resolving the error. Resubmitting the transactions with the profile options:
‘MRP: Debug Mode’ set to ‘Yes’ may provide even more information in the log file.
Please enable inventory debug per Metalink Note.148651.1.
The details follow for your convenience:
INV: Debug Trace = YES
INV: Debug Level = 11
INV: Debug File = Please set it with the full path where you would like to write the debug messages. By default, it will be written in /tmp/invdbg.db.
Please ensure the debug file is set with a directory existing in utl_file_dir that can be found with this sql:
*** Warmning ***
Verify transactions to be resubmitted since there may be Move Order Transactions that must be processed through the Transact Move Order Form.
SQL>UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET PROCESS_FLAG = ‘Y’,
LOCK_FLAG = ‘N’,
TRANSACTION_MODE = 3,
ERROR_CODE = NULL
WHERE TRANSACTION_HEADER_ID = ‘& TRANSACTION_HEADER_ID’;
SQL> SELECT value
FROM v$parameter
WHER name = 'utl_file_dir';
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 Transactions
In 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.
Cost > View Transactions > Material Transactions
Pending Transactions Guide 7 of 32
Cost > View Transactions > Material Transactions
By using the Material Transaction window, you can view, edit, correct and resubmit erred costed transactions. There are five tabs to select the type of information:
Location, Intransit, Reason, Reference, Transaction ID and Transaction Type which provide detailed information to help you resolve erred and uncosted transactions.
The Main Descriptive fields give detailed information on they type of transaction:
Item Description,
Inventory item description
Primary UOM,
Transaction quantity in terms of the primary uom of the item
Primary Qty,
Transaction quantity in terms of primary uom of the item
Date,
Transaction date
Distributions
(to view distributions of costed transactions) Pending Transactions Guide 8 of 32
Lot/Serial
(Used if transaction is associated with a Lot or Serial number)
Fields common to all Tabs:
Item,
Inventory item reference by the line item
Transaction UOM,
Unit of measure used in the transaction
Transaction Quantity,
Quantity of this transaction
Transaction ID,
Displays the item, transfer transaction ID
Transaction Date,
Date the transaction was entered for processing
Source Type,
Source type of the transaction, such as WIP Job or Schedule
Source,
Source of the transaction, such as account number
Transaction Type,
Displays the transaction type
Fields in the Location Tab are as follows:
Subinventory,
Subinventory referenced by the line items
Locator,
Locator referenced by the line item
Revision,
Revision of the inventory item referenced by the line item
Transfer Subinventory,
Destination subinventory
Transfer Locator,
Destination Locator
Owning Org,
Owning organization identifier
Planning Org,
Planning organization identifier
Supplier,
Supplier
Primary Quantity,
Transaction quantity in terms of primary uom of the item
Primary UOM,
Transaction quantity in terms of the primary uom of the item
Fields in the Intransit Tab are as follows:
Shipment Number,
Shipment number for the line item
Waybill/Airbill,
The waybill or airbill number for the transfer
Freight Code,
Freight carrier for the transaction
Containers,
Number of containers in which the material is stored for the transfer
Fields in the Reason, Reference Tab are as follows:
Reason,
The transaction reason
Reference,
Reference text describing the transaction
Costed,
Indicates the costing status
Transferred to Projects,
Indicates the transfer to cost status
Error Code,
Code describing the error on the last attempt to process the line items
Error Explanation,
Full explanation of the error that occurred when attempting to process the line item
Supplier Lot,
supplier lot number.
Source Line ID,
Line level transaction source id Pending Transactions Guide 9 of 32
Fields in the Transaction ID Tab are as follows:
Transfer Transaction ID,
Transaction id of the opposing transfer transaction
Transaction Header,
Number used to group transactions in the concurrent manager
Receiving Transaction ID,
Receiving transaction cross reference
Move Transaction ID,
Move transaction cross reference
Completion Transaction ID,
Completion transaction cross reference
Department Code,
Oracle Work in Process department code for the line item
Operation Sequence,
Operation sequence number within a routing
Source Project Number,
the source project number
Source Task Number,
the source task number
Project Number,
the project number
Task Number,
the task number
To Project Number,
the to project number
To Task Number,
the to task number
Expenditure Type,
the expenditure type
Expenditure Org.,
the expenditure organization
Error Code,
code describing the error on the last attempt to process the line item
Error Explanation,
full explanation of the error that occurred when attempting to process the line item
Fields in the Transaction Type tab are as follows:
Transaction Action,
the transaction action
Resubmitting Uncosted Transactions:
Before re-submitting records through the Material Transactions Form, you can gather information regarding your data and the Inventory Transaction Managers and workers. The following Diagnostic Scripts should be used to identify data, errors, and status of concurrent and interface managers/workers.
Inventory Period Close Diagnostic Test - for the period in question
(INVPeriodClose115.sql) Note: 206576.1
Uncosted Transaction Check - Run from the operating system prompt
(CstCheck.sql) Note.246467.1
For more debug information use the following:
‘MRP: Debug Mode’ set to ‘Yes’ may provide even more information in the log file.
Pending Transactions Guide 10 of 32
In the Material Transactions Form failed transactions can be resubmitted by clicking on "Tools", "Select All", and then clicking on "Tools, Submit all". Or Submit the number of transactions found through the search criteria in the find window. The erred and uncosted transactions can also be resubmitted through the following SQL*Plus statement:
First shut down the Cost Manager:
The "Cost Manger" (module CMCTCM) is the manager that will launch the "Material cost transaction worker" (module CMCMCW) for a standard costing environment or the "Actual Cost Worker" (module CMCACW) for an average, fifo, lifo or perpetual costing environment. If the error message in the Material Transaction form is not clear or not available reviewing the log file of the "Cost Worker" may provide more information that could help in resolving the error.
In an Average, FIFO or LIFO costing organization if there is one transaction that is in error the Cost Manager will not launch a Cost Worker to process the uncosted transactions. This is standard functionality because in an Average, FIFO or LIFO costing organization the item cost is recalculated during receiving transactions and if they were not costed in order the item cost would not be correct.
In the Organization Parameter screen under the Costing Information tab there is a field "Cost Cutoff Date" and a Cost Manager will not launch any Cost Workers past this date. This is most commonly used to update item cost and all transactions past the cost cutoff date will be transacted at the new cost, once the Cost Cutoff Date is changed.
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.
SQL> UPDATE MTL_MATERIAL_TRANSACTIONS
SET COSTED_FLAG = ‘N’, TRANSACTION_GROUP_ID = NULL
WHERE COSTED_FLAG is not null;
Pending Transactions Guide 11 of 32
WIP > Resource Transactions > Pending Resource Transactions
In the Pending Resource Transaction window you can view, update and resubmit resource transactions that have failed validation and remain in the WIP_COST_TXN_INTERFACE table. You can also resubmit and delete transactions whose concurrent process has failed and have a processing phase of Complete and a process status of Error. There are seven tabs to select the type of information:
Failed transactions will have the Transaction Id And Group ID populated and the Status will be "Error". Pending transactions will have the Transaction ID and Group ID fields blank and the Status will be "Pending".
To view error details for failed resource transactions, select the transaction and click on the [Error] button. The Pending Resource Transaction error window appears.
(WIP_COST_TXN_INTERFACE) THAT FAILED VALIDATION.
Processing, Source, Concurrent Request, Job or Schedule Name, Operation, Resource, Transaction, Comments and Project Information which provide detailed information to help in resolving pending resource transactions. Column indicates the name of the column in the resource transaction interface table Message indicates why the transaction failed. Pending Transactions Guide 12 of 32
Fields Common to all Tabs:
Transaction ID,
Transaction Identifier
Fields in Processing Tab:
Group Id,
Transaction group identifier
Phase,
Transaction processing phase
Status,
Transaction processing status
Fields in Source Tab:
Source Code,
Code of the shop floor control system that generated the transaction record (null if created by Oracle Work in Process)
Source Line,
Identifier used by source shop floor control system (null if created by Oracle Work in Process)
Created By,
Username of person that created the record
Fields in Concurrent Request Tab:
Request ID,
Request Id
Phase,
Transaction processing phase
Status,
Transaction processing status
Message,
Concurrent Message
Fields in Job or Schedule Name Tab:
Organization,
Organization identifier
Job or Schedule Name,
WIP job or repetitive assembly name
Line,
WIP line identifier
Assembly,
Inventory item identifier of assembly that job or schedule creates
Fields in Operation Name Tab:
Op Seq,
Operation sequence number within a routing
Dept,
Department Identifier
Res Seq,
Resource sequence number
Name,
Resource code
Type,
Resource type
Employee Number,
Employee number
Fields in Resource Tab:
Activity,
Activity name
Charge Type,
Method of charging the resource
PO Number,
Purchase order Identifier that the receipt is against Pending Transactions Guide 13 of 32
Currency,
Unique Identifier for the currency
Actual Rate,
Actual rate of the resource
Fields in Transaction Tab:
UOM,
Unit of measure used for transaction
Quantity,
Transaction quantity
Date,
Date transaction was performed
Type,
Transaction type
Fields in Comments Tab:
Reason,
Standard transaction reason name
Reference,
Transaction reference descriptive text
Fields in Project Information Tab:
Project Number,
Project Identifier
Task Number,
Project task Identifier
Resubmitting Failed or Pending Resource Transactions
Before re-submitting records through the Pending Resource Transactions Form, you can gather information regarding your data and the Inventory Transaction Managers and workers. The following Diagnostic Scripts should be used to identify data, errors, and status of concurrent and interface managers/workers.
Inventory Period Close Diagnostic Test - for the period in question
(INVPeriodClose115.sql) Note: 206576.1
In the Pending Resource Transactions window either check the transaction’s Resubmit check box to resubmit one record or chose Select All for Resubmit from the Special Menu and selectively deselect individual transaction you do not want to resubmit, save your work. Erred transactions can also be resubmitted via the following SQL statement:
This will resubmit records in PROCESS_STATUS = 3 (Error), nulling out the transaction id, group id and concurrent request id and change the process status to pending.
The "Cost Manager" (short name CMCTCM) is the manager that will launch the "Resource Cost Worker" (short name CMCCTW) or the "Overhead Cost Worker" (short
SQL> UPDATE WIP_COST_TXN_INTERFACE
SET GROUP_ID = NULL,
TRANSACTION_ID = NULL,
REQUEST_ID = NULL,
PROCESS_STATUS = 1
WHERE PROCESS_STATUS = 3;
name CMCOCW) to process the resources or resource overhead transactions for a standard costing environment. In an Average, LIFO, FIFO, or Perpetual costing environment the "Cost Manager" will launch an "Actual Cost Worker" (short name CMCACW) for processing resources or resource overhead transactions. If the error message in the Pending Resource Transaction screen widow is not clear, reviewing the log file of the "Cost Worker" may provide more information that could help in resolving the error. Changing the profile opting "MRP: Debug Mode" set to Yes and then resubmit the erred resource transactions and the log file may provide more information on the error.
Uncosted WSM txn Transactions
Uncosted WSM transactions in the WSM_SPLIT_MERGE_TRANSACTIONS table, preventing a period from closing can be viewed through the WIP Lot Transactions form:
Shop Floor Manager > Lot Transactions > WIP Lot Transactions
Clicking on the ‘Flash Light’ icon will bring up the find window. Selecting Null in the
‘Transaction Type’ field will select all WIP Lot Transactions, selecting something other than complete in the ‘Costed’ field and the ‘Transaction Dates’ for the period to query uncosted transactions.
Pending Transactions Guide 15 of 32
Shop Floor Manager > Lot Transactions > WIP Lot Transactions
By using the WIP Lot Transaction window, you can view uncosted transactions. If there is more than one uncosted transactions clicking on the down arrow will toggle through these transactions. There are five tabs to select detailed information on the WIP Lot;
Job Bill, Routing, Operations and Others.
The main descriptive fields give detailed information on the transaction:
Transaction Id,
Transaction Identifier
Transaction Date,
Date transaction was performed
Status,
Transaction processing status
Transaction Type,
Transaction Type Identifier
Job Type,
Type of WIP Job
Costed,
Transaction costing status
Reason,
Standard transaction reason identifier
Reference,
Transaction reference descriptive text Pending Transactions Guide 16 of 32
Fields common to all Tabs:
Job Name,
WIP Job Name
Representative Job,
Representative Job Flag
Fields in Job Tab:
Description,
Description of the discrete job or repetitive assembly
Type,
Type of WIP Job
Assembly,
Inventory item Identifier for the assembly the job or schedule creates
Quantity,
Available Quantity
MRP Net Quantity,
MRP Net Quantity
Fields in Bill Tab:
Reference,
Assembly identifier used as bill of material reference for explosions of nonstandard job
Alternate,
Alternate BOM Designator used in a Job
Rev,
BOM revision for the primary assembly
Revision Date,
Date of assembly item revision used to explode the bills of material used in the job
Fields in the Routing Tab:
Reference,
Assembly identifier used as routing reference for explosions of nonstandard job
Alternate,
Alternate Routing Designator used in a Job
Rev,
Routing revision for the primary assembly
Revision Date,
Routing revision date
Subinventory,
Default completion subinventory for assemblies completed by the job
Locator,
Default completion locator identifier for assemblies completed by the job
Fields in Operation Tab:
Job Seq,
Operation Sequence Number
Code,
Standard Operation Code