Inventory Transaction Transaction Interface Managers
Subject: Release 10.7 and 11.0: Inventory Transaction Interface Managers Doc ID: ID: Note:66633. Type: BULLETIN 1 Last Revision21-FEBStatus: PUBLISH Date: 2005 ED This document was checked for relevance on 25-FEB-2005 PURPOSE This article provides technical overviews and details of the four interface managers that can be controlled from the Interface Managers form as part of the setup procedures: 1. Material transaction manager 2. Demand reservation manager 3. Cost Manager 3. Move transaction manager
SCOPE & APPLICATION This bulletin is intended for system administrators of Oracle Applications and users that are involved with setting up Oracle Inventory.
RELATED DOCUMENTS Oracle Inventory Reference Manual vol.3 - Inventory Transactions Oracle Inventory User guide Chapter 5 - Transaction Setup
These four managers execute the following processes: material transaction, demand reservation,
resource & material cost transactions and move transactions. Generally these managers are running continuously at regular process intervals as determined on starting the managers. They control the number of transaction transaction workers, processing intervals and number of transactions processed by the worker. The mode of operation for these managers is governed by the setting of profile options prefixed by the letters TP (transacti (transaction on processing). o
for example: example :
Setting profile TP:INV Transaction Processing Mode to a value of Background Processing, will mean that Inventory and WIP transactions are periodically transacted upon by the Material transaction manager. The transaction manager requests will appear on the concurrent managers queue at regular processing intervals.
Resolving Problems -----------------As transactions are processed, the respective inventory and work in progress tables are updated to reflect these changes. If for some reason the results viewed in inquiry forms and reports do not reflect these updates, it will then be necessary to review and correct the errors. This can be done in part using the Transaction Interface & Pending Transations forms, however there are often cases when sqlplus will be required to effect a resolution, in these cases it may be helpful to refer to the notes below.
resource & material cost transactions and move transactions. Generally these managers are running continuously at regular process intervals as determined on starting the managers. They control the number of transaction transaction workers, processing intervals and number of transactions processed by the worker. The mode of operation for these managers is governed by the setting of profile options prefixed by the letters TP (transacti (transaction on processing). o
for example: example :
Setting profile TP:INV Transaction Processing Mode to a value of Background Processing, will mean that Inventory and WIP transactions are periodically transacted upon by the Material transaction manager. The transaction manager requests will appear on the concurrent managers queue at regular processing intervals.
Resolving Problems -----------------As transactions are processed, the respective inventory and work in progress tables are updated to reflect these changes. If for some reason the results viewed in inquiry forms and reports do not reflect these updates, it will then be necessary to review and correct the errors. This can be done in part using the Transaction Interface & Pending Transations forms, however there are often cases when sqlplus will be required to effect a resolution, in these cases it may be helpful to refer to the notes below.
Material Transaction Interface -----------------------------o
INCTCM module: Process transactions interface
This program processes material transactions following changes in any of the transactions form. For optimal processing speed the system manager responsibility should be used to tune the Inventory Manager. The initial setup for this is defaulted with a single target process setup. The number of target processes will limit the number of workers available to the transaction manager, workers can be run in parallel, therefore to significantly reduce the time taken to process your inventory transactions, then increase the number of target processes to the maximum allowable within your system limits. For this request the assistance of your dba, system memory and central processor performance will be the limiting factors for this target parameter. While running, the program: INCTCM, constantly checks for rows in the interface and interface temporary tables. First checking the interfaces temporary tables and acting upon these records and then processing the records in the interface tables, marking these records with header identifiers and subsequently moving them to the corresponding temporary tables, this process is repeated each time the workers are started. For example: INCTCM polls and transacts upon records held in mtl_transactions_interface,
mtl_transaction_lots_interface ,and mtl_serial_numbers_interface moving these to their respective temp tables, eg: mtl_material_transactions_temp , these are then processed by the transaction processor, such rows have their lock_flag set to the value Y. Different workers are submitted by the transaction processor to process Order Entry, Work in Progress and Inventory transactions,these are processed in groups, depending on the value of the flag validation_required, values 1 or null = detailed validation 2=not detailed processed rows are assigned a transaction_header_id transaction_header_id using the sequence mtl_material_transactions_s. o
Records that are ready for selection will have:
transaction_mode=3, transaction_ mode=3,
process_flag=1 or Y for
mtl_material_transactions_temp lock_flag=2 or null(or N in mtl_material_transactions_temp) process_flag has a range of values:
1=ready,2=not 1=ready,2=not
ready,3=failed lock_flag has a range of values:
1=locked 2=not
locked,NULL=not locked transaction_mode transaction_ mode has a range of values:
2=immediate
mode 3=backgound mode
The rows in mtl_transactions_interface are processed in 5 phases. 1. Derives dependant columns, eg:acct_period_id, primary_quantity etc.. 2. Detailed validation performed on the records 3. On hand qty check for negative qty's etc.. 4. Reservations Relieved if demand was created in order entry
5. Rows are moved to mtl_material_transactions_temp where the transaction processor is called to process these rows and update the inventory levels etc..
For more details see vol 3 INV technical reference manual.
o
Resolving Errors:
If an error occurs with the transaction processor, the entire group of records will be marked with the error code, the actual failing row will contain the error explanation. You can access failing rows using Inventory's View Pending Interface Activity form, additionally you can use this form to resubmit the records by setting the 'resubmit' flag to yes, this is only appropriate if the error that caused the failure is unrelated to the interfaces transaction data, eg: database space problem. Errors can be determined by viewing columns: error_code and error_explanation on table mtl_transactions_interface, once the cause has been corrected, use sql plus to update the process_fag = 1 and the lock_flag = 2 or null During phases 1-3 above, rows are marked with an error code and explanation, for phase 4 all records with this header_id will be marked as errored regardless. For phase 5 all rows will be marked with the same error except for the single bad row that caused the failure.
Demand Reservation Manager -------------------------o
INCDCM module: Process demand interface
The Demand Reservation Manager periodically updates Inventory with the demands from sales orders shipments. INCDCM is linked into INVLIBR, the program processes records from mtl_demand_interface to mtl_demand table. Records held in mtl_demand_interface need to have process_flag=1 (ready), transaction_mode=3 and error_code = null. For more details on this program see page 3-812 vol 3 INV technical reference manual. o
Resolving Errors:
Regardless of pass or failure, all records failing validation in the interface table will be marked with an error code.
Columns
error_code and err_explanation can be selected for explanation of the cause. A succesful demand operation is denoted by error_code=0. Full errors listings can be determined by selecting, lookup_code and meaning from the table mfg_lookups where lookup_type='MTL_DEMAND_INTERFACE_ERRORS' For more details on the table mtl_demand_interface
vol 1
Oracle Inventory Technical Reference manual.
Material & Resource Cost transaction processor (Cost Manager) ------------------------------------------------------------
The Cost Manager controls both the Material & Resource Cost Transaction Processors. This manager starts the processors at the specified process interval in the launch manager form, The Cost manager itself does not kick off a concurre3nt request for each of the intervals, the manager only runs (goes from pending to running then back to pending) once every 24 hours. Material Cost Processor ----------------------o
CMCMCW module: Material Cost Transaction
Processor This program is used to process material transactions for costing in inventory and WIP products.
This module scans table
mtl_material_transactions for uncosted transactions, ie: where costed_flag='N'& transaction_group_id=NULL. For each transaction found program; INLTCP is called to process the records. Note: If this program is not running then there will be no available Transaction Distribution details in the Material Distributions inquiry form. Selects are made on mtl_serial_numbers_temp, using this info, updates and inserts will be made on mtl_serial_numbers. When this process is complete, the records are deleted from mtl_serial_numbers_temp. Similar processes are carried out on tables mtl_transaction_lots_temp and mtl_lot_numbers Records are inserted into mtl_transaction_accounts, for the account and the value of the transactions. On successfully processing the transactions in
mtl_material_transactions_temp, these records are then deleted. For more details on this program see vol 3 Oracle Inventory Technical Reference manual. Resource Cost Processor --------------------o
CMCCTM module: Resource cost transaction manager
The Resource cost transaction manager processes transactions from Oracle's WIP in addition to those resource transactions imported from other sorces such as barcode readers. The program polls pending transactions in wip_cost_txn_interface and periodically updates/inserts into the following WIP tables: wip_transactions, wip_transaction_accounts and wip_period_balances The program can be run from the command line: CMCCTM bom/bom passwd 0 Y For more details on this program see Oracle WIP Technical Referance manual.
WIP Move Transaction Manager ---------------------------o
WICTMS module:
The WIP Move Transaction Manager processes move transactions in Oracle WIP and move transactions from devices such as bar code readers. The table wip_move_txn_interface is polled by the program WICTMS to determine if any transactions are pending validation and or processing.
-----------------------------------------------------------------------------------------------------------------------------------------Unprocessed Transactions/Closing INV Accounting Period FAQ
Subject: Unprocessed Transactions/Closing INV Accounting Period FAQ Doc ID: Note:110424 Type: BULLETI .1 N Last Revision18-FEB-2005 Status: PUBLISH ED Date: PURPOSE ------Provide basic understanding about Oracle Inventory Transactions and Cost processing procedure.
SCOPE & APPLICATION ------------------Oracle manufacturing customers, support, and consulting. Transactions - Unprocessed Transactions/Closing INV Accounting Period (TRXN-STK) This document will be used to explain the flow of transactions from the MTL_TRANSACTIONS_INTERFACE to the GL_INTERFACE. The aim of this document is to help and assist in resolution of Interface related issues.
CONTENTS -------Q1.
What is the MTL_TRANSACTIONS_INTERFACE table used
for? Q2.
What is the MTL_MATERIAL_TRANSACTIONS_TEMP table
used for? Q3.
What type of issues will you see with the
MTL__TRANSACTIONS_INTERFACE and
MTL_MATERIAL_TRANSACTIONS_TEMP tables? Q4.
What type of issues will you see with the
MTL_MATERIAL_TRANSACTIONS table? Q5.
What type of issues will you see with the
MTL_TRANSACTION_ACCOUNTS table? Q6.
Where do you find Pending/Errored Inventory
Transactions? Q7.
Where do you find Pending/Errored WIP Move and WIP
Resource Transactions? Q8.
What are the major tables involved in Material
Movement for Inventory and WIP Material, Move, and Resource Transactions? Q9.
How do you resolve Pending Material Transactions?
Q10. How do you resolve Pending WIP Cost Transactions? Q11. When attempting to load legacy data into the MTL_TRANSACTIONS_INTERFACE table, into what column is the PO number input? Q12. Why is the distribution account ID required in MTL_TRANSACTIONS_INTERFACE?
FAQ: Frequent Asked Questions ============================= Q1. What is the MTL_TRANSACTIONS_INTERFACE table used for? A1. This is the Interface between non-Inventory applications and the Inventory Transactions module. In other words any other module other than Inventory that wants to update Inventory has to come through this table. Modules such as WIP (Work In Progress) and OE (Order Entry) first pass their records to the MTL_TRANSACTIONS_INTERFACE (MTI)for validation. There is an Interface Manager called the Transactions Manager (INCTCM) which reads records from this table, validates them and moves the successful transactions onto MTL_MATERIAL_TRANSACTIONS_TEMP, and submits Transaction workers (sub-processes - INCTCW) which then processes these records through
inventory. This process consists of data derivation, validation, and transfer of records from MTL_TRANSCTIONS_INTERFACE, MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE into their respective TEMP (temporary) tables from where the transactions processor processes them. Both the Lots and Serial number tables above are used when items being updated are under 'Lot' or 'Serial' number control. Example :- In the case of an Sales Order, the item is being shipped to a client so the lot and serial number if being used, this needs to be updated to show that it is no longer available in Inventory stock. It is important to note that in general the processors will not move the transactions from this table if the following fields are not set as follows. LOCK_FLAG = 2 PROCESS_FLAG = 1 transaction_mode = 3
Q2.
What is the MTL_MATERIAL_TRANSACTIONS_TEMP table
used for? A2.
Once the transactions have been passed to this table
after initial validation by the Transactions Manager (INCTCM) from the MTL_TRANSACTIONS_INTERFACE a job id is attached and a Transactions Worker (INCTCW) is submitted by the INCTCM process in order tothe get the records processed and moved to the MTL_MATERIAL_TRANSACTONS table.
This table is also used by Inventory Module and Purchasing module which writes directly onto this table for any transactions entered within itself and each transaction in turn through a process of strict validation. Inventory Module Forms like Miscellaneous transactions writes directly into this table. The transactions which are done through these form are on-line processing. It is from here that the inventory quantities finally get updated,serial/lot numbers get marked as being used.
Q3.
What type of issues will you see with the
MTL__TRANSACTIONS_INTERFACE and MTL_MATERIAL_TRANSACTIONS_TEMP tables? A3.
The issues can be related to stuck transactions in
these tables. The following is the list of errors that you may see in error_code, error_explanation columns in the above tables: NOTE: In Release 11 the APP-XXX errors do not show any more. ERROR_CODE
ERROR
EXPLANATION APP-05367
Invalid Item
APP-05341
Invalid sub-
inventory APP-05343
Invalid
transaction date APP-05345 transaction UOM
Invalid
APP-05354
Invalid
locator segments APP-05365
Invalid
distribution account segments APP-05372
Transaction
processor error APP-05526
Invalid
locator APP-05518
Negative
balances not allowed APP-05064
Quantity must
be less than or equal to available to transact for that item at its specific control level APP-05520
No open period
found for date entered Account period Lot records Serial records
APP-05367 Invalid Item Check the attributes against the set required for the transaction type. i.e. Order entry, PO etc. APP-05341 Invalid Subinventory Check and make sure that this record does not already exist on the MTL_MATERIAL_TRANSACTIONS_TEMP table as sometimes the process does not clear some of the records out of MTL_TRANSACTIONS_INTERFACE correctly. If this is the case it is a simple matter of deleting the records from the
MTL_TRANSACTIONS_INTERFACE.
Check the subinventory against the available MTL_SECONDARY_INVENTORIES on the system for the this transaction.
given ORGANIZATION_ID against
If the subinventory exists, check the TRANSACTION_TYPE_ID, for example: If it is a Purchase Order then you need to check the MTL_SUPPLY to ensure that the supply exists for this transaction. APP-05343 Invalid transaction date In certain circumstances and depending on the transaction type that the future period is not open. There is no issue in changing the date to the current date and resubmitting it through the system. APP-05345 Invalid transaction UOM Check the UOM measure being used and find out if valid for the organization source. If not set up the UOM. It may also be a case where the conversion classes have not been set-up. APP-05354 Invalid locator segments APP-05355 The Locator segments are invalid for the given sub inventory, organization Combination. Check under Inventory Responsibility -> set-up -> flexfeilds -> segments
key -> 'stock locators',check each segment has
been enabled. APP-05365 Invalid distribution account segments The distribution account segments has its base in the Account Key Flexfield set-up. It is this Flexfield structure that determines how many segments should be populated into the segments are MTL_TRANSACTION_INTERFACE table. Basically in order to check if the distribution account correct we need to join the details back to the GL_CODE_COMBINATION table to validate the account code being used for the transaction.Inquiry on the error_explanation
field as this may identify what segment is actually incorrect.This might return result APP-1756 Value 'XXXX' is not defined. Need to check that the segments point to a valid row in GL_CODE_COMBINATIONS. APP-05372 Transaction processor error For this error you really need to get the ERROR_EXPLANATION to find out the exact message as to why it is possibly failing. These can usually have their three flags reset and resubmitted as this error is usually attached to those records in a group where one of the records has been erred with one of the more severe errors. APP-05526 Invalid locator There is a list of checks that you need to make to ensure that the above error is correct. 1.Extract Organization_id and cross check tables to see if the org is under locator control. 2.Are the locator details filled incorrectly on the transaction 3.Has the Organisation set-up been modified. APP-05518 Negative balances not allowed. This message will be prompted if there is not enouhg on hand quantity to satisfy all the transactions within a batch. Therefore, ensure that the transaction quantity is enough to satisfy the sum of all the transaction quantities for a particular batch. APP-05064 Quantity must be less than or equal to Available to
transact for that item at its specific control level. (For Example: subinventory,locator,lot,serial) 1. If you specify a subinventory/locator while entering the order, it means we just wants to ship it out from that subinventory/locator and if there is not enough available to reserve/transact in that particular subinventory/locator the inventory program will error out with the above errors.
However, if
there are no subinventories/locators specified while entering the order, we will pick up from where its available in that Organization. 2. Check onhand and availabe quantity for the errored items. 3. The records would have errored out due to some other row in the batch not having enough stock, so the entire batch would fail, find the row that has error explanation populated. Resubmit all the other rows except the one which has error explanation , if the same error occurs for other rows, do the same process once again excluding that row. 4. Please note that Available Quantity = On Hand Quantity - Reservations (Soft and Hard Reservations). APP-05520 No open period found for date entered Period is not currently open for the transaction to be costed against later. 1.Check that the period is open 2.Check that the date is a date between the open and close period.
Q4.
What type of issues will you see with the
MTL_MATERIAL_TRANSACTIONS table? A4.
Transaction is valued (unit cost*quantity = $
amount) Once data gets here the transaction is almost complete except for the fact that it needs to have the costing details finally updated and written to the Transaction Accounts table for transfer to General Ledger. This is a transactional historical table. However, it is the holding point for transactions to be cost. The Cost Manager is a concurrent job that runs in the background. (CMCTCM) COSTED_FLAG = 'N'(Not Processed)
This means record
is pending. If it is NULL then the transaction has already been processed. If it is set to 'E' then it has erred for some reason.
Q5. What type of issues will you see with the MTL_TRANSACTION_ACCOUNTS table? A5. The details of the transactions are usually written into this table as Debits and Credits and from here ct can kick off Transfer to GL Navigation Path -> Inventory Responsibility -> Accounting Close Cycle -> General Ledger Transfers) as a concurrent request takes the transactions with a GL_BATCH_ID of -1 and passes them to the
GL_INTERFACE for processing. When it passes the transactions to the GL_INTERFACE it is at this time that the batch is given it's proper batch number. Now there is a new functionality introduced. When transfer to GL is done, there is a column called GROUP_ID which gets populated in gl_interface table with gl_batch_id. GL BATCH identifier is used in the GROUP ID field, only the one batch which is in error would be prevented from being imported and posted. Also, adding a GROUP ID would allow for a more definitive level of control not only across locations but within a location. Patches which gives this functionality: REL 10.7: 853450 REL 11.0: 1114961
Q6.
Where do you find Pending/Errored Inventory
Transactions? A6.1. Navigate to Inventory Responsibility -> Accounting Close Cycle -> Inventory Accounting Periods -- Select Pending Button related to an OPEN period. -- In the top region -- Resolution Required -- If it show pending records, These must be fixed prior to trying to close the period. -- form only shows a count of pending records against the period. A6.2. Navigate Inventory Responsibility -> Transactions -> Transaction Open Interface -- This form queries the MTL_TRANSACTIONS_INTERFACE table -- Records can be resubmitted from this form.
A6.3. Navigate Inventory Responsibility -> Transactions -> Pending Transactions -- This form queries the MTL_MATERIAL_TRANSACTIONS_TEMP table -- Records can only be resubmitted via the form. -- Records must be processed out of this form in order to close inventory accounting periods. A6.4. Navigate Inventory Responsibility -> Transactions -> Material Transactions -- Records can be viewed from this form to know whether the transactions has costed flag as 'YES' or 'No', also other related columns under Alternate Region -> 'Reason/Reference'. -- We re-submit the records only through SQL.
Q7.
Where do you find Pending/Errored WIP Move and WIP
Resource Transactions? A8.1. Navigate Work in Process Responsibility -> Move Transactions -> Pending Move Transactions -- This form queries the WIP_MOVE_TXN_INTERFACE table -- Records can be updated, deleted, and resubmitted via the form. A8.2. Navigate Work in Process Responsibility -> Resource Transactions -> Pending Resource Transactions -- This form queries the WIP_COST_TXN_INTERFACE table -- Records can be updated, deleted, and resubmitted via the form.
Q8.
What are the major tables involved in Material
Movement for Inventory and WIP Material, Move, and Resource Transactions?
A8.
See below: MTL_TRANSACTIONS_INTERFACE -- INCTCM is the Transaction Manager for this
Table. MTL_MATERIAL_TRANSACTIONS_TEMP -- INCTCM is the Transaction Manager for this Table 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. WIP_MOVE_TXN_INTERFACE -- Wip Move Transaction Worker processes records in this table (WICTCM) WIP_MOVE_TRANSACTIONS -- Must be viewed and resubmitted via SQL with the scripts below WIP_COST_TXN_INTERFACE -- Resource Cost Worker processes records in this table.
Q9.
How do you resolve Pending Material Transactions?
A9.
Resolving Pending Material Transactions is a process
of determining and fixing what is preventing a record from being processed through the MTL_TRANSACTIONS_INTERFACE table. Details of pending transactions can be viewed through the application by navigating to the Transaction Open Interface form.
Using the Transaction Open Interface window, you can view, edit, correct and resubmit transactions received through the open interface. There are five selections in the alternate region list of values: Error, Location, Source, Intransit and Other. These provide detailed information to help you resolve pending transactions. In the Error alternate region: a. The error code describes the error on the last attempt to process the line item. b. The error explanation gives a reason for the error. c. The process flag indicates whether the row has been processed by the concurrent manager. The process flag status codes are: 1 = Pending 2 = Running 3 = Error Resubmitting Transactions for Processing: In the Transaction Open Interface window, check the Resubmit [] box next to the transaction you want to resubmit or choose Resubmit All from the Special menu. If you have many transactions to resubmit, use the Resubmit All button to select all transactions for processing and then selectively deselect individual transactions you do not want to resubmit. Save your work to submit the transactions for processing.
Q10. How do you resolve Pending WIP Cost Transactions?
A10. Resolving Pending WIP Cost transactions is a process of determining and fixing what is preventing a record from being processed through the WIP_COST_TXN_INTERFACE table Pending transactions can be viewed through the application by navigating to the WIP Responsibility -> pending resource transaction form. In the Pending Resource Transaction window you can view, update, delete, and resubmit resource transactions that have failed validation and remain in the WIP_COST_TXN_INTERFACE table. You can also resubmit transactions whose concurrent process has failed and have a processing phase of Complete and process status of Error. There are eight selections in the alternative region list of values: Processing, Source, Concurrent Request, Job or Schedule Name, Operation, Resource, Transaction and Comments. Error 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 erred transaction and click on the [Error] button. The Pending Resource Transaction error window appears. Column indicates the name of the column in the resource transaction interface table (WIP_COST_TXN_INTERFACE)that failed validation. Message indicates
why the transaction failed. To resubmit failed resource transactions: In the Pending Resource Transactions widow either check the transaction's Resubmit check box to resubmit one record or choose select All for Resubmit from the Special Menu then save your work.
Q11. When attempting to load legacy data into the MTL_TRANSACTIONS_INTERFACE table, into what column is the PO number input? A11. There is not a direct column for PO number.In the Oracle Manufacturing Implementation Manual, locate the section on Open Transactions Interfaces.
This section describes the transaction
type id's and there is not a PO number. Refer to the column of transaction_reference and this column will allow the po number. The column is optional and shows up on reports and transaction displays. Since a column for the PO number does not directly exist,a transaction_reference field can be used to input this information. In Release 10.7, we do a Miscellaneous Receipt (PO Receipts are not supported). In Release 11, Purchasing Module has a Receiving Interface and we do not see any data coming to this table anymore. Customers that currently have release 10.7 and do not have this functionality will need to upgrade to release 11.0 or higher,
which contains the production version of this functionality.
Q12. Why is the distribution account ID required in MTL_TRANSACTIONS_INTERFACE? A12. All issues and receipts of asset items to an asset subinventory and sales order shipments require the distribution account id that originates from GL code combinations. This account id is required to create a complete audit trail of the transaction.
RELATED DOCUMENTS ----------------Oracle Inventory User's Guide
---------------------------------------------------------------------------------------------------------------------
Sample WMS Open Interface Insert Scripts / Examples howto Insert into Transaction Open Interface
Subject: Sample WMS Open Interface Insert Scripts / Examples howto Insert into Transaction Open Interface Doc ID: Note:357535. Type: HOWTO 1 Last03-AUG-2006 Status: MODERAT ED Revision Date: In this Document Goal Solution References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to: Oracle Warehouse Management - Version: 11.5.8 to 11.5.10 Information in this document applies to any platform. MTL_TRANSCTIONS_INTERFACE, MTL_TRANSACTIONS_LOTS_INTERFACE , MTL_SERIAL_NUMBERS_INTERFACE
Goal The purpose of this note is to provide common transaction open interface examples showing how to correctly submit different transactions through transaction open interface. Some debugging tips are also provided. The following scenarios are addressed: How can the transaction open interface be used in the following scenarios? Scenario: 1 Account alias receipt for an item that is neither Lot or Serial control. Scenario: 2 Account alias issue for an item that is neither Lot or Serial control. Scenario: 3 Account alias receipt for an item that is Lot controlled. Scenario: 4 Account alias receipt for an item that is Serial controlled Scenario: 5 Subinventory transfer for a lot controlled item. Scenario: 6 Miscellaneous receipt using LPN for an item under serial control Scenario: 7 Miscellaneous issue using LPN for an item under serial control Scenario: 8 Container pack item that is not Lot/Serial Control Into LPN Scenario: 9 Container Un pack from LPN
Solution Use the transaction open interface process to do different transactions to do onhand conversions from legacy systems,and do other transactions like subinventory transfer , account alias issue / receipts etc.. The transactions could involve Lot and /or serial controlled items. It could also involve LPN related
transactions if the items are packed in LPN’s (if the customer is using warehouse management system[wms]). In this note we will walk you through some basic transactions that could be done through transaction open interface. The 3 main tables that we would concentrate on are mtl_transactions_interface , mtl_transaction_lots_interface and mtl_serial_numbers_interface. Once you have set up Inventory Concurrent Manager you can launch Inventory Transactions Manager (INCTCM) through Interface Managers Window which reads records from mtl_transactions_interface table, validates them and moves the successful transactions onto MTL_MATERIAL_TRANSACTIONS_TEMP, and submits Transaction workers (sub-processes - INCTCW) which then processes these records through inventory. This process consists of data derivation, validation, and transfer of records from MTL_TRANSCTIONS_INTERFACE, MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE into their respective TEMP (temporary) tables from where the transactions processor processes them. Mtl_Transactions_Interface table is the key table through which user can submit transactions The 3 main columns that needs to be set for the transaction processor to process the records. LOCK_FLAG = 2 PROCESS_FLAG = 1 transaction_mode = 3 TRANSACTION_INTERFACE_ID column is required for transactions of items under lot or serial control. The value in the column in this table is used to identify the child rows in the lot or serial interface tables MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE. If the transacted item is under lot control and/or serial control this
column maps to MTL_TRANSACTION_LOTS_INTERFACE.TRANSACTION_INTERFACE_ID and MTL_SERIAL_NUMBERS_INTERFACE.TRANSACTION_INTERFACE_ID.
For more information on the tables please refer to Oracle Manufacturing API's and Open Interfaces Manual Scenario: 1
Testing the transaction open interface with minimum columns populated. Account alias receipt for an item that is neither Lot or Serial control . We have defined an item bmw and we have assigned the item to W1 Cherry hill organization. We are going to receive 10 quantity into subinventory FGI and locator '1.1.1'. Below is the sql to check organization id for organization W1 . SQL> select organization_code,organization_id from org_organization_definitions where organization_code = 'W1'; ORG ORGANIZATION_ID --- --------------W1 1884 Below is the sql to check the inventory_item_id for out item bmw . SQL> select inventory_item_id , segment , organization_id from mtl_system_items_b where segment = 'bmw' and organization_id = 1884; INVENTORY_ITEM_ID SEGMENT ORGANIZATION_ID ----------------- ---------------------------------------- --------------10531 bmw 1884 Below is the sql to find out the transaction_type_id for transaction type Account alias receipt.
SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types where transaction_type_name = 'Account alias receipt'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME ------------------- -----------------------------DESCRIPTION --------------------------------------------------------------------------------------------------41 Account alias receipt Receive material against account alias
Below is the script to get the alias account name and the account id it is linked to. Segment is the account alias name. SQL> select disposition_id, description,segment , distribution_account from mtl_generic_dispositions where organization_id = 1884; DISPOSITION_ID DESCRIPTION -------------- -------------------------------------------------SEGMENT DISTRIBUTION_ACCOUNT ---------------------------------------- -------------------147 Default Account DEFAULT 17021 Below is the sql to get the locator id for locator ‘1.1.1’ associated with subinventory ‘FGI’. SQL> select inventory_location_id,segment1, segment2, segment3,subinventory_code from mtl_item_locations where organization_id = 1884 and subinventory_code = 'FGI' and segment1 = '1' and segment2 = '1' and segment3 = '1'; INVENTORY_LOCATION_ID SEGMENT1 --------------------- ---------------------------------------SEGMENT2 SEGMENT3 SUBINVENTO ---------------------------------------- ---------------------------------------- ----------
&nbsbsp; 1
1291 1 &nbnbsp;
1
FGI
Below is the script to find the segments(account c) for the distribution acc021. to account alias screen in Inventory and verify the account for the alias that is defined. SQL> select code_combination_id, segment1 , segment2, segment3 , segment4 , segment5 from gl_code_combinations where code_combination_id = 17021 ; CODE_COMBINATION_ID SEGMENT SEGMENT2 SEGMENT3 ------------------- ------------------------- ------------------------------------------------SEGMENT4 SEGMENT5 ------------------------- ------------------------17021 01 520 5250 0000 000 Below is the complete sql to do account alias receipt. The values populated are based on the values obtained from above sqls. Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_source_name, transaction_source_id, transaction_quantity , primary_quantity ,
transaction_type_id , dst_segment1, dst_segment2, dst_segment3, dst_segment4, dst_segment5) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date 'Alias Receipt', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 10531 , --inventory item id 'FGI', --From subinventory code 1884, --organization id ' DEFAULT ', --transaction source 147, --transaction source id 10, --transaction quantity 10, --Primary quantity 41, --transaction type id '10 ', --segment account combination '520', --segment2 account combination '5250', --segment3 account combination '0000', --segment4 account combination '000' --segment5 account combination ); commit; Scenario: 2 Account alias issue for an item that is neither Lot or Serial control. We are issuing out 10 qty that we had received earlier. The subinventory is FGI and the locator is'1.1.1'. We are using the same account alias ' DEFAULT '.
Below is the sql to find out the transaction_type_id for transaction type Account alias issue. SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types where transaction_type_name = 'Account alias issue'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME ------------------- -----------------------------DESCRIPTION --------------------------------------------------------------------------------------------------31 Account alias issue Issue material against account alias Below is the complete sql to do account alias issue. Note that the transaction type id is 3 for account alias issue and as we are issuing 10 quantity out, the transaction quantity is - 10. Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_source_name, transaction_source_id, transaction_quantity , primary_quantity , transaction_type_id , dst_segment1, dst_segment2, dst_segment3, dst_segment4,
dst_segment5) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date 'Alias Issue', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 10531 , --inventory item id 'FGI', --From subinventory code 1884, --organization id ' DEFAULT ', --transaction source 147, --transaction source id -10, --transaction quantity 10, --Primary quantity 31 , --transaction type id '01', --segment1 account combination '520', --segment2 account combination '5250', --segment3 account combination '0000', --segment4 account combination '000' --segment5 account combination ); commit; Scenario: 3
Account alias receipt for an item that is Lot controlled. We have defined an item bmwlot and we have assigned the item to W1 Cherry hill organization. We are going to receive 10 quantity into subinventory FGI and locator '1.1.1'.The lot number we are going to assign is LT0001.
SQL> select inventory_item_id , segment , organization_id, auto_lot_alpha_prefi , start_auto_lot_number from mtl_system_items_b
where segment = 'bmwlot' and organization_id = 1884; INVENTORY_ITEM_ID SEGMENT ORGANIZATION_ID ----------------- ---------------------------------------- --------------AUTO_LOT_ALPHA_PREFIX START_AUTO_LOT_NUMBER ------------------------------ -----------------------------10532 bmwlot 1884 LT 0001 Below is the complete sql to do account alias receipt. The values populated are based on the values obtained from above sqls. Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_source_name, transaction_source_id, transaction_quantity , primary_quantity , transaction_type_id , dst_segment1, dst_segment2, dst_segment3, dst_segment4, dst_segment5, transaction_interface_id) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date
'Alias Receipt', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 10532, --inventory item id 'FGI', --From subinventory code 1884, --organization id ' DEFAULT ', --transaction source 147, --transaction source id 10, --transaction quantity 10, --Primary quantity 41 , --transaction type id '01', --segment1 account combination '520', --segment2 account combination '5250', --segment3 account combination '0000', --segment4 account combination '000', --segment5 account combination 999--transaction interface id );
Insert into mtl_transaction_lots_interface ( transaction_interface_id, lot_number, Lot_expiration_date, transaction_quantity, last_update_date , last_updated_by , creation_date , created_by ) VALUES (999 , 'LT00001', sysdate+100, 10, sysdate , 0, sysdate ,
0 ); commit; Scenario: 4
Account alias receipt for an item that is Serial controlled. We have defined an item bmwserial and we have assigned the item to W1 Cherry hill organization. We are going to receive 10 quantity into subinventory FGI and locator '1.1.1'. The serial numbers we are going to assign is from SR0001 to SR0010.
SQL> Select inventory_item_id , segment1 , organization_id, auto_serial_alpha_prefix, start_auto_serial_number from mtl_system_items_b where segment1 = 'bmwserial' and organization_id = 1884; INVENTORY_ITEM_ID SEGMENT1 ORGANIZATION_ID ----------------- ---------------------------------------- --------------AUTO_SERIAL_ALPHA_PREFIX START_AUTO_SERIAL_NUMBER ------------------------------ -----------------------------10534 bmwserial 1884 SR 0001
Below is the complete sql to do account alias receipt. The values populated are based on the values obtained from above sqls. Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code,
organization_id, transaction_source_name, transaction_source_id, transaction_quantity , primary_quantity , transaction_type_id , dst_segment1, dst_segment2, dst_segment3, dst_segment4, dst_segment5, transaction_interface_id) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date 'Alias Receipt', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 10534, --inventory item id 'FGI', --From subinventory code 1884, --organization id ' DEFAULT ', --transaction source 147, --transaction source id 10, --transaction quantity 10, --Primary quantity 41 , --transaction type id '01', --segment1 account combination '520', --segment2 account combination '5250', --segment3 account combination '0000', --segment4 account combination '000', --segment5 account combination 999--transaction interface id ); Insert into mtl_serial_numbers_interface (transaction_interface_id,
fm_serial_number, to_serial_number, last_update_date, last_updated_by, creation_date, created_by) values (999, --transaction interface_id 'SR0001', --from serial number 'SR0010' , --to serial number sysdate, --last update date 0, --last updated by sysdate, --creation date 0 --created by ); commit;
Scenario: 5
Subinventory transfer for a lot controlled item. We have defined an item bmwlot and we have assigned the item to W1 Cherry hill organization. We have already received 10 quantity into subinventory FGI and locator '1.1.1'. We are planning to move 2 quantities from subinventory FGI locator '1.1.1' to subinventory 'TCLOOSE' and locator 'TCLO.1.1.'
Below is the sql to find locator id for locator 'TCLO.1.1' SQL> select inventory_location_id,segment1, segment2, segment3,subinventory_code from mtl_item_locations where organization_id = 1884 and subinventory_code = 'TC-LOOSE'; INVENTORY_LOCATION_ID SEGMENT1 --------------------- ---------------------------------------SEGMENT2 SEGMENT3 SUBINVENTO ---------------------------------------- ---------------------------------------- ---------1482 TCLO 1 1 TC-LOOSE Below is the sql to find the transaction type id for transaction type 'Subinventory Transfer'
SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types where transaction_type_name ='Subinventory Transfer'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME ------------------- -----------------------------DESCRIPTION --------------------------------------------------------------------------------------------------2 Subinventory Transfer Transfer material between subinventories INSERT INTO MTL_TRANSACTIONS_INTERFACE ( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag, locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_quantity , primary_quantity , transaction_type_id , transfer_subinventory, transfer_locator, transaction_interface_id) VALUES ( 'Ea', --transaction uom sysdate, --transaction date 'Subinventory Transfer', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode
2 , --lock flag 1291 , --locator id sysdate, --last update date 0 , --last updated by sysdate, --created date 0 , --created by 10532 , --inventory item id 'FGI', --subinventory code 1884, --organization id 2 , --transaction quantity 2 , --primary quantity 2, --transaction type id 'TC-LOOSE', -- from subinventory 1482, -- from locator id 999); --transaction interface id INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE ( transaction_interface_id, lot_number, Lot_expiration_date, transaction_quantity, last_update_date , last_updated_by , creation_date , created_by) VALUES (999, --transaction interface id 'LT00001', --Lot number sysdate+100, --Lot expiration date 2, --transaction quantity sysdate, --last update date 0, --last updated by sysdate, --creation date 0); --created by commit; Scenario: 6
Miscellaneous receipt using LPN for an item under serial control Overview of License Plate Numbers (LPN):
LPN's are used in Oracle Warehouse Management System (WMS).Oracle WMS provides full visibility to inventory items that are stored in LPNs. An LPN in Oracle WMS is any object that exists in a location and holds items. Although LPNs are associated with containers, they do not need to represent a physical identity, such as a box. Thus, a LPN might be defined as just a label or a collection of items. Oracle WMS enables you to track, transact and nest LPNs and their contents. Using License Plate Numbers: The Oracle WMS LPN features enable you to do the following: a. Receive, store, and pick material by LPN b. View on-hand balances by LPN c. View contents of LPN, including item number, serial number, quantity, and so on d. Move many items in a transaction using the LPN e. Print labels and reports for referencing container contents f. Track nested LPNs (for e ample, cartons on a pallet) G. Pack, unpack, consolidate, split, and update LPNs h. Reuse empty LPNs
Points to remember when using LPN s (License Plate Numbers):
LPN_ID : LPN from which an Unpack operation is to be done TRANSFER_LPN_ID : LPN to which a Pack operation is to be done CONTENT_LPN_ID field is populated for Issue transactions and the TRANSFER_LPN_ID for Receipt transactions (LPN_ID field in MTI should NOT be used for these transactions) We cannot issue individual serials from an LPN through an Issue transaction. If an Misc. Issue is done for an LPN the ENTIRE LPN and ALL ITS CONTENTS are issued out. If the you want to issue out a single serial from an LPN, the you must first unpack the serial from thesting an unpack transaction and then issue the individual serial. In our scenario we will receive item bmwlpnserial into LPN A using miscellaneous receipt. We are going to receive 10 quantity of the item into subinventory FGI and locator'1.1.1'. We have generated the LPN using Generate LPN program through Warehouse Manger responsibility and the concurrent program is under Other> Requests
> Run. You can check the LPN number in the log file of the Generate LPN program. Below is the sql to check the LPN information then Generate LPN program. SQL> select lpn_id,license_plate_number,lpn_content 2 from wms_license_plate_numbers 3 where license_plate_number = 'LPN1A'; LPN_ID LICENSE_PLATE_NUMBER LPN_CONTEXT --------- ------------------------------ ----------128 LPN1A 5 Below is the sql to find out the meaning for the LPN content. SQL> select lookup_code, meaning from mfg_lookups where lookup_type ='WMS_LPN_CONTEXT' order by lookup_code; LOOKUP_CODE MEANING ----------- -------------------------------------------------------------------------------1 Resides in Inventory 2 Resides in WIP 3 Resides in Receiving 4 Issued out of Stores 5 Defined but not used 6 Resides in Intransit 7 Resides in Vendor 8 Packing content 9 Loaded to Dock 10 Prepack for WIP 11 Picked We are going to use LPN A which has a content 5 Defined but not used . SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types where transaction_type_name = 'Miscellaneous receipt'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME ------------------- -----------------------------DESCRIPTION -----------------------------------------------------------------------------------------------
----42 Miscellaneous receipt Perform miscellaneous receipt of material SQL> select inventory_item_id , segment , organization_id from mtl_system_items_b where segment1 = 'bmwlpnserial' and organization_id = 1884; INVENTORY_ITEM_ID SEGMENT ORGANIZATION_ID ----------------- ---------------------------------------- --------------11821 bmwlpnserial 1884 Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_quantity , primary_quantity , transaction_type_id , dst_segment1, dst_segment2, dst_segment3, dst_segment4, dst_segment5, transaction_interface_id, transfer_lpn_id ) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date
'Miscellaneous Receipt', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 11821 , --inventory item id 'FGI',--From subinventory code 1884, --organization id 10, --transaction quantity 10, --Primary quantity 42, --transaction type id '01', --segment1 account combination '520', --segment2 account combination '5250', --segment3 account combination '0000', --segment4 account combination '000', --segment5 account combination 999, --transaction interface id 128); --transfer lpn id Insert into mtl_serial_numbers_interface (transaction_interface_id, fm_serial_number, to_serial_number, last_update_date, last_updated_by, creation_date, created_by) values (999, --transaction interface_id 'SR00001' , --from serial number 'SR00010', --to serial number sysdate, --last update date 0, --last updated by sysdate, --creation date 0 --created by ); commit;
After the import when you check the content of lpn LPN1A you will see that the content has changed to Resides in Inventory . SQL> select lpn_id,license_plate_number,lpn_content from wms_license_plate_numbers where license_plate_number = 'LPN1A'; LPN_ID LICENSE_PLATE_NUMBER LPN_CONTEXT --------- ------------------------------ ----------28 LPN1A Scenario: 7
Miscellaneous issue using LPN for an item under serial control
We are going to do miscellaneous issue of the lpn LPN A and its entire contents that we had received in scenario 6. SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types 2 where transaction_type_name = 'Miscellaneous issue'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME ------------------- -----------------------------DESCRIPTION --------------------------------------------------------------------------------------------------32 Miscellaneous issue Perform miscellaneous issue of material Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by ,
creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_quantity , primary_quantity , transaction_type_id , dst_segment1, dst_segment2, dst_segment3, dst_segment4, dst_segment5, transaction_interface_id, content_lpn_id) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date 'Miscellaneous Issue', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 2 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 11821, --inventory item id 'FGI', --From subinventory code 1884, --organization id -10,--transaction quantity 10, --Primary quantity 32, --transaction type id '01', --segment1 account combination '520', --segment2 account combination '5250', --segment3 account combination '0000', --segment4 account combination '000', --segment5 account combination 999, --transaction interface id 128); --transfer lpn id
Insert into mtl_serial_numbers_interface (transaction_interface_id, fm_serial_number, to_serial_number, last_update_date, last_updated_by, creation_date, created_by) values (999, --transaction interface_id 'SR00001' , --from serial number 'SR00010' , --to serial number sysdate, --last update date 0, --last updated by sysdate, --creation date 0 ; --created by ); commit; SQL>select lpn_id,license_plate_number,lpn_content from wms_license_plate_numbers where license_plate_number = 'LPN1A'; LPN_ID LICENSE_PLATE_NUMBER LPN_CONTEXT --------- ------------------------------ ----------128 LPN1A 4 After the issue transaction when you check the content of lpn LPN1A you will see that the content has changed to 4 Issued out of Stores Scenario: 8
Container pack item that is not Lot/Serial Control Into LPN
Generated an LPN using Generate LPN program. Now we are going to pack item bmwp which we have defined and has received loose qty (10) in subinventory FGI and locator'1.1.1' into lpn 'LPN21A' . SQL>select lpn_id,license_plate_number,lpn_content from wms_license_plate_numbers where license_plate_number = 'LPN21A';
LPN_ID LICENSE_PLATE_NUMBER LPN_CONTEXT --------- ------------------------------ ----------135 LPN21A 5 We are going to use LPN21A which has a content 5 Defined but not used SQL>select inventory_item_id , segment , organization_id from mtl_system_items_b where segment = 'bmwp' and organization_id = 1884; INVENTORY_ITEM_ID SEGMENT ORGANIZATION_ID ----------------- ---------------------------------------- --------------11822 bmwp 1884 SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types where transaction_type_name = 'Container Pack'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME -------------------------------------------------------------------------------------------------DESCRIPTION --------------------------------------------------------------------------------------------------87 Container Pack Container Pack The transaction type id for Container Pack is 87 Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date ,
created_by , inventory_item_id , subinventory_code, organization_id, transaction_quantity , primary_quantity , transaction_type_id , transfer_lpn_id ) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date 'Container Pack', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE,--last update date 0, --last updated by SYSDATE , --creation date 0, --created by 11822, --inventory item id 'FGI', --From subinventory code 1884, --organization id 10, --transaction quantity 10, --Primary quantity 87, --transaction type id 135); --transfer lpn id commit; SQL> select license_plate_number,lpn_content,subinventory_code, locator_id, sealed_status from wms_license_plate_numbers where license_plate_number = 'LPN21A'; LICENSE_PLATE_NUMBER LPN_CONTEXT SUBINVENTO LOCATOR_ID SEALED_STATUS ------------------------------ ----------- ---------- ---------- ------------LPN21A 1 FGI 1291 2 LPN content is 1 which means Resides in Inventory .
Scenario: 9 Container Un pack from LPN
We are going to unpack lpn LPN21A which we had packed in scenario 8. SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types 2 where transaction_type_name = 'Container Unpack'; TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME ------------------- -----------------------------------------------------------------------DESCRIPTION -----------------------------------------------------------------------------------88 Container Unpack Container Unpack
The transaction type id for Container UnPack is 88 Note that we are using column LPN_ID and NOT TRANSFER_LPN_ID as we are doing an un pack transaction. Insert into mtl_transactions_interface( transaction_uom, transaction_date, source_code, source_line_id, source_header_id, process_flag , transaction_mode , lock_flag , locator_id , last_update_date , last_updated_by , creation_date , created_by , inventory_item_id , subinventory_code, organization_id, transaction_quantity , primary_quantity , transaction_type_id , lpn_id
) VALUES ( 'Ea', --transaction uom SYSDATE, --transaction date 'Container UnPack', --source code 99, --source line id 99, --source header id 1, --process flag 3 , --transaction mode 2 , --lock flag 1291 , --locator id SYSDATE , --last update date 0, --last updated by SYSDATE , --creation date 0, --created by 11822, --inventory item id 'FGI', --From subinventory code 1884, --organization id 10, --transaction quantity 10, --Primary quantity 88, --transaction type id 135); --lpn id commit; SQL> select license_plate_number,lpn_content,subinventory_code, locator_id, sealed_status from wms_license_plate_numbers where license_plate_number = 'LPN21A'; LICENSE_PLATE_NUMBER LPN_CONTEXT SUBINVENTO LOCATOR_ID SEALED_STATUS ------------------------------ ----------- ---------- ---------- ------------LPN21A 5 2
You can see that LPN21A content is 5 Defined but not used and the subinventory and locator id is null in wms_license_plate_numbers table. Frequently Asked Questions (FAQ): a. How do I know what error has occurred? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^
Ans . The process_flag in mtl_transactions_interface will be 3. Error_Code and Error Explanation these two columns would usually explain why the error has occurred. The process flag indicates whether the row has been processed by the concurrent manager. The process flag status codes are: 1 = Pending 2 = Running 3 = Error
b. What things should I check if I getting errors? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^ Ans. First check if you are populating the correct values in all the fields. Then check if there are any mandatory columns. Many times user forgets to populate mandatory values and transaction import fails. Check if the item is transactable or not by going to organization items screen. Also check for invalid objects Select object_name,object_type from all_objects where status = 'INVALID'; If any invalid objects please recompile those and retest transactions import.
In case you are using serial numbers please check the following: The MTL_TRANSACTIONS_INTERFACE.TRANSACTION_QUANTITY will either match the sum of the records in the MTL_SERIAL_NUMBERS_INTERFACE.TRANSACTION_INTERFACE_ID for a specific MTL_TRANSACTIONS_INTERFACE.TRANSACTION_INTERFACE_ID, or the FM_SERIAL_NUMBER and TO_SERIAL_NUMBER will have the accurate range to match the MTL_TRANSACTIONS_INTERFACE.TRANSACTION_QUANTITY for a particular record.
In case you are using Lot numbers please check the following: Please check to see for a specific MTL_TRANSACTIONS_INTERFACE.TRANSACTION_INTERFACE_ID there is a corresponding record in mtl_transaction_lots_interface with matching mtl_transaction_lots_interface.transaction_interface_id c. How do I resubmit the errored transactions? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^
1.Resubmission through the Application Navigation Inventory:Transactions:Transaction Open Interface Click in the check box under the "Submit" column then Save 2. Resubmission through SQL*Plus. SQL>Update MTL_TRANSACTIONS_INTERFACE Set PROCESS_FLAG = 1, LOCK_FLAG = 2, TRANSACTION_MODE = 3, VALIDATION_REQUIRED = 1, ERROR_CODE = NULL, ERROR_EXPLANATION = NULL Where PROCESS_FLAG IN (1,3);
d. What do I do to check more in to errors and see where the import is failing? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Below are some documents that support requires when customer creates a tar for MTI related issues.
a.Getting log files that would give more details as to where error is happening. 1.Please enable inventory debug per Note.148651.1. The details follow for your convenience: To generate a log file, set profile values for the user performing the transaction.
a) these directories are write enabled by the DBA; SELECT value FROM v$PARAMETER WHERE name = 'utl_file_dir'; b) set the following profile values INV: Debug Trace = YES INV: Debug File = [directory value from above query]/logfilename.log (make sure that you have write permission for this file and directory) INV: Debug Level = 11 2. Go to System administration > Concurrent > Program > define. Query on short name 'INCTCM' or program name 'Process transaction interface'. Check enable trace and save. 3. Go to System administration > Concurrent > Program > define. Query on short name 'INCTCW' or program name 'Inventory transaction worker'. Check enable trace and save. 4. Run the inventory worker. 5. Provide the resulting worker log and inventory debug IF ONE IS CREATED. b. Collecting organization information. Oracle Inventory Support Service Request Instrumentation for Organization Setup - for inventory (INVCt115h.sql) Note:190893.1 c. Collecting Item information. Oracle Inventory Item Setup Data Collection Tool - for the related item (INVItemCt115h.sql ) Note: 223702.1 d. (INVMTI115h.sql) Note: 209928.1 Assists in diagnosing and resolving pending transactions in Release 11i from the MTL_TRANSACTIONS_INTERFACE table which could prevent further proper processing of Inventory records. Identifies records that are in error or have failed validation and provides some solutions.
e. (INVMMTT115h.sql) Note: 204577.1 Assists in diagnosing and resolving pending transactions in Release 11i from the MTL_MATERIAL_TRANSACTIONS_TEMP f. Provide file versions for the following files All errors will require the transaction manager file versions. To get these versions, please run the following query: set serveroutput on; execute fnd_aolj_util.getClassVersionfromDB('oracle.apps.inv.transaction.ser ver.BaseTransaction'); exe ute fnd_aolj_util.getClassVersionfromDB('oracle.apps.inv.transaction.ser ver.QtyManager'); execute fnd_aolj_util.getClas VersionfromDB('oracle.apps.inv.transaction.server.LotTrxManager'); execute fnd_aolj_util.getClassVersionfromDB('oracle.app .inv.transaction.server.SerTrxManager'); execute fnd_aolj_util.getclassversionfromdb('oracle.apps.inv.keyentities.Seria lNumber'); INVTXMGB.pls INVTXGGB.pls TrxProcessor.java BaseTransaction.java inltev.ppc inctcm.ppc INVTXMGB.pls INVTXMGS.pls SerialNumber.java SerTrxManager.java $AU_TOP/resource/INVSLENT.pll inltis.ppc inumer.ppc inutsu.ppc inlmsn.ppc You could use a command like the following: strings -a $AU_TOP/resource/INVSLENT.pll |grep '$Header' strings -a $INV_TOP/bin/INVLIBR |grep inltis.ppc
