Inventory Transaction Interface庫存表

智者

Inventory Transaction Interface Managers[1].doc (159 KB, 下载次数: 344)

2007/1/25 15:01 上传

点击文件名下载附件 　本附件是我收集的INV中部分表的作用及其咝羞^程，拿來與大家分享！！！

Inventory Transaction Interface Managers

Subject:	Release 10.7 and 11.0: Inventory Transaction Interface Managers
	Doc ID:	Note:66633.1	Type:	BULLETIN
	Last Revision Date:	21-FEB-2005	Status:	PUBLISHED

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 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 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:       1=ready,2=not ready,3=failed

lock_flag has a range of values:          1=locked 2=not locked,NULL=not locked

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.1	Type:	BULLETIN
	Last Revision Date:	18-FEB-2005	Status:	PUBLISHED

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

    on the system for the  given ORGANIZATION_ID against this transaction.

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

    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 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.1	Type:	HOWTO
	Last Revision Date:	03-AUG-2006	Status:	MODERATED

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;            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 , 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 '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 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.server.BaseTransaction');
exe ute
fnd_aolj_util.getClassVersionfromDB('oracle.apps.inv.transaction.server.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.SerialNumber');

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
strings -a $INV_TOP/bin/INVLIBR |grep inlmsn.ppc
strings -a $INV_TOP/lib/libinv.a |grep inumer.ppc
strings -a $INV_TOP/lib/libinv.a |grep inutsu.ppc

Terence

Thanks for your share!

bhbb2000

呵呵，不错。这种资料也可以找到

forest

强人,感谢楼主,,帮你顶个

hbwp2008

3KS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

sunweijie22

多谢多谢阿！！！！

selena_kk

怎么总是不让我下载呢？

zhongguooracle

謝謝你的分享﹗﹗﹗

ECHO

不錯學習學習

ZHANGIVAN

谢谢,先下来侃侃....

lisa.tang

正在研究库存!!

alice1113

先谢谢拉，虽然我还没下，因为努力值不够了

alice1113

UPUPUPUPUP

andy_tao198565

頂﹐樓主﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗

dingding1983

thank you  for you help

xinyuliu

学习的好资料，谢谢

xxuuvv

good,thanks

tommy_gu

tks~收一下~

zks2008

谢谢分享!支持!

wing365

这么好的资料，，可惜下载不了。。