InventoryTransaction Interface Managers

Subject: Release 10.7 and 11.0: Inventory

Transaction Interface Managers
Doc ID66633
1
Last Revision 21-FEB- PUBLISH
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
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 (transaction processing).

o for 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 Transactions 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_interfacemoving 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 using the sequence
mtl_material_transactions_s.

o Records that are ready for selection will have:

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: ready

ready
lock_flag has a range of values: locked
not locked
transaction_mode has a range of values: immediate
mode 3 = background mode

The rows in mtl_transactions_interface are processed in 5

phases.

Derives dependent columns, e.g.: acct_period_id,

primary_quantity etc..
2. Detailed validation performed on the records
3. On hand quantity check for negative quantities etc.
4. Reservations Relieved if demand was created in order
entry
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,
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 successful
Demand operation is denoted by error_code=0.

Full errors listings can be determined by selecting,

lookup_code
from the table mfg_lookups where
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 concurrent
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 sources 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
work in progress period balances

The program can be run from the command line: CMCCTM

good/good password 0 Y

For more details on this program see Oracle WIP Technical

Reference 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

Unprocessed Transactions/Closing INV

Accounting Period FAQ
Doc ID110424
.1 N
18-FEB-2005
Date:ED

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?
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?

Frequently 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
The 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 a 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
A job ID is attached to MTL_TRANSACTIONS_INTERFACE and
a Transactions

Worker (INCTCW) is submitted by the INCTCM process in

order to the
get the records processed and moved to the
MTL_MATERIAL_TRANSACTIONS
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 Invalid
transaction UOM
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

SECONDARY_INVENTORIES
on the system for the given ORGANIZATION_ID against
this transaction.
 If the subinventory exists, check the
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,
Combination

Check under Inventory Responsibility -> set-up ->

flexfeilds -> key ->
segments '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
find out the exact message as to why it is possibly
failing.
These can usually have their three flags reset and re-
submitted
as this error is usually attached to those records in
a group
where one of the records has been mistaken 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
Has the organization setup been modified.

APP-05518 Negative balances not allowed.

This message will be prompted if there is not enough
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 want to ship it out from that.
subinventory/locator and if there is not enough
available to
reserve/transact in that particular
subinventory/locator the
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 it's
available
in that Organization.

2. Check on-hand and available 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 the 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.
Check that the period is open
[Link] 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)

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 provides this functionality:

REL 10.7: 853450
REL11.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 shows 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 have 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:

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.

Oracle Manufacturing
Implementation Manual, locate the section on Open
Transactions
Interfaces. This section describes the transaction
type ids 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.
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, the 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 how to Insert into Transaction Open
Interface

Subject: Sample WMS Open Interface Insert

Scripts / Examples how to Insert into
Transaction Open Interface
Doc ID357535
1
Last 03-AUG-2006 Status: MODERATE
Revision ED
Date:

In this Document
Goal
Solution
References

This document is being delivered to you via Oracle Support'sRapid 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 nor
Serial control.
Scenario: 2 Account alias issue for an item that is neither Lot nor
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 Unpack 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

InventoryTransactions Manager (INCTCM) through Interface

Managers Window
which reads records from the 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 need to be set for the transaction.
processor to process the records.
LOCK_FLAG = 2
PROCESS_FLAG = 1
transaction_mode = 3

The 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 nor 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 the 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 the 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
----------------- ---------------------------------------- ---------------
10531 bmw 1884

Below is the SQL to find out the transaction_type_id for the transaction.
Account alias receipt.
select
from mtl_transaction_types
where transaction_type_name = 'Account alias receipt';

TRANSACTION_TYPE_ID
------------------- ------------------------------
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
-------------- --------------------------------------------------
SEGMENT DISTRIBUTION_ACCOUNT
---------------------------------------- --------------------
147 Default Account
DEFAULT17021

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
--------------------- ----------------------------------------
SEGMENT2 SEGMENT3
SUBINVENTORY
---------------------------------------- ---------------------------------------- ----------
 &nbsbsp; 1291 1
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
from gl_code_combinations
where code_combination_id = 17021 ;
CODE_COMBINATION_ID
------------------- ------------------------- -------------------------
-------------------------
SEGMENT4
------------------------- -------------------------
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
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 nor Serial
control.
We are issuing out 10 units that we had received earlier.
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 the transaction.
type Account alias issue.

transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Account alias issue';
TRANSACTION_TYPE_ID
------------------- ------------------------------
DESCRIPTION
-----------------------------------------------------------------------------------------------
-----
31 Account alias issue
Issue material against account alias

Below is the complete SQL to perform 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 (
Each, --transaction unit of measure

SYSDATE, --transaction date

Alias Issue
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 isLT0001.

SQL> select inventory_item_id, segment, organization_id,

auto lot alpha prefix
from mtl_system_items_b
where segment = 'bmwlot'
and organization_id = 1884;

INVENTORY_ITEM_ID
----------------- ---------------------------------------- ---------------
AUTO_LOT_ALPHA_PREFIX
------------------------------ ------------------------------
10532 bmwlot 1884
T
0
L0
1

Below is the complete SQL to perform 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 (
Each
SYSDATE, --transaction date
Alias Receipt
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
from mtl_system_items_b
where segment1 = 'bmwserial'
and organization_id = 1884;
INVENTORY_ITEM_ID
----------------- ---------------------------------------- ---------------
AUTO_SERIAL_ALPHA_PREFIX
------------------------------ ------------------------------
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 (
Each
SYSDATE, --transaction date
Alias Receipt
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 quantities.
into subinventory FGI and locator '1.1.1'. We are planning to move 2
quantities from subinventory FGI locator '1.1.1' to subinventory 'TC-
LOOSE' 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
--------------------- ----------------------------------------
SEGMENT2 SEGMENT3 SUBINVENTORY
---------------------------------------- ---------------------------------------- ----------
1482 TCLO
1 1 TC-LOOSE

Below is the SQL to find the transaction type ID for transaction type.
Subinventory Transfer
transaction_type_id
from mtl_transaction_types
where transaction_type_name ='SubinventoryTransfer';
TRANSACTION_TYPE_ID
------------------- ------------------------------
DESCRIPTION
-----------------------------------------------------------------------------------------------
-----
2 SubinventoryTransfer
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 (
Each
sysdate, --transaction date
SubinventoryTransfer
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
sysdate+100, --Lot expiration date
2, --transaction quantity
sysdate, --last update date
0, --last updated by
sysdate, --creation date
0); --created by

commit;

Miscellaneous receipt using LPN for an item under serial

control

Overview of License Plate Numbers (LPN):

LPNs 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 example, cartons on a pallet)
G. Pack, unpack, consolidate, split, and update LPNs
h. Reuse empty LPNs

Points to remember when using LPNs (License Plate Numbers):

LPN from which an Unpack operation is to be done

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 a Misc. Issue is done for an LPN the ENTIRE LPN and
ALL ITS CONTENTS are issued out. If you want to issue out a
single serial from an LPN, then you must first unpack the serial from
testing 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 Manager
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.

select lpn_id, license_plate_number, lpn_content

2 from wms_license_plate_numbers
3 where license_plate_number = 'LPN1A';
LPN_ID
--------- ------------------------------ -----------
128 LPN1A 5

Below is the SQL to find out the meaning for the LPN content.

lookup_code
from mfg_lookups
where lookup_type = 'WMS_LPN_CONTEXT'
order by 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.

select transaction_type_id, transaction_type_name, description

from mtl_transaction_types
where transaction_type_name = 'Miscellaneous receipt';

TRANSACTION_TYPE_ID
------------------- ------------------------------
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
----------------- ---------------------------------------- ---------------
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 (
Each
SYSDATE, --transaction date
Miscellaneous Receipt
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
--------- ------------------------------ -----------
28 LPN1A

Scenario: 7

Miscellaneous issue using LPN for an item under serial

control

We are going to address miscellaneous issues related to LPN A and its

entire contents that we had received in scenario 6.

transaction_type_id
from mtl_transaction_types
2 where transaction_type_name = 'Miscellaneous issue';

TRANSACTION_TYPE_ID
------------------- ------------------------------
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 (
Each
SYSDATE, --transaction date
Miscellaneous Issue
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
--------- ------------------------------ -----------
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 the Generate LPN program. Now we are going
to pack item bmwp which we have defined and has received loose
Move 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
--------- ------------------------------ -----------
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
----------------- ---------------------------------------- ---------------
11822 bmwp 1884

transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Container Pack';

TRANSACTION_TYPE_ID
-------------------
--------------------------------------------------------------------------------
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 (
Each
SYSDATE, --transaction date
Container Pack
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
from wms_license_plate_numbers
where license_plate_number = 'LPN21A';

LICENSE_PLATE_NUMBER
SEALED_STATUS
------------------------------ ----------- ---------- ---------- -------------
LPN21A 1 FGI 1291 2

LPN content is 1 which means Resides in Inventory.

Scenario: 9

Container Unpack 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
------------------- ------------------------------------------------------------------------
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 (
Each
SYSDATE, --transaction date
Container UnPack
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
from wms_license_plate_numbers
where license_plate_number = 'LPN21A';

LICENSE_PLATE_NUMBER
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
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:

Pending
2 = Running
3 = Error

b. What things should I check if I am 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 there are any invalid objects, please recompile them and retest the 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,
NULL
ERROR_EXPLANATION = NULL
Where PROCESS_FLAG IN (1,3);

d. What do I do to check more into errors and see where the

Is the import 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 the 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
DebugTrace = YES
INV: Debug File = [directory value from above
query]/[Link] (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.

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
([Link]) Note:190893.1

c. Collecting Item information.

Oracle Inventory Item Setup Data Collection Tool - for the related
item
([Link] ) Note: 223702.1

d.
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.
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('[Link]
[Link]');
execute
fnd_aolj_util.getClassVersionfromDB('[Link]
[Link]
execute
fnd_aolj_util.getClas
Version from DB('[Link]');
execute
fnd_aolj_util.getClassVersionfromDB('[Link]
.[Link]
execute
fnd_aolj_util.getclassversionfromdb('[Link]
lNumber');

[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]

[Link]
[Link]
$AU_TOP/resource/[Link]
The provided text does not appear to be translatable. Please provide valid text for translation.

You could use a command like the following:

strings -a $AU_TOP/resource/[Link] | grep '$Header'
strings -a $INV_TOP/bin/INVLIBR | grep [Link]
strings -a $INV_TOP/bin/INVLIBR | grep [Link]
strings -a $INV_TOP/lib/libinv.a | grep [Link]
strings -a $INV_TOP/lib/libinv.a | grep [Link]