12 EBS R12 1 INV - Table InformationeTRM

Prévia do material em texto

For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
<Insert Picture Here>
Title of Presentation
Presenter’s Name
Presenter’s Title
Oracle E-Business Suite R12.1 Partner Boot Camp Training 
Oracle Inventory Management Boot Camp
Inventory Management – Inventory Table Information and eTRM
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
The following is intended to outline our general 
product direction. It is intended for information 
purposes only, and may not be incorporated into any 
contract. It is not a commitment to deliver any 
material, code, or functionality, and should not be 
relied upon in making purchasing decisions.
The development, release, and timing of any 
features or functionality described for Oracle’s 
products remains at the sole discretion of Oracle.
Safe Harbor Statement
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Use of this Site (“Site”) or Materials constitutes agreement with the following terms and conditions: 
1. Oracle Corporation (“Oracle”) is pleased to allow its business partner (“Partner”) to download and copy 
the information, documents, and the online training courses (collectively, “Materials") found on this Site. The 
use of the Materials is restricted to the non-commercial, internal training of the Partner’s employees only. 
The Materials may not be used for training, promotion, or sales to customers or other partners or third 
parties. 
2. All the Materials are trademarks of Oracle and are proprietary information of Oracle. Partner or other third 
party at no time has any right to resell, redistribute or create derivative works from the Materials. 
3. Oracle disclaims any warranties or representations as to the accuracy or completeness of any 
Materials. Materials are provided "as is" without warranty of any kind, either express or implied, including 
without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement. 
4. Under no circumstances shall Oracle or the Oracle Authorized Delivery Partner be liable for any loss, 
damage, liability or expense incurred or suffered which is claimed to have resulted from use of this Site of 
Materials. As a condition of use of the Materials, Partner agrees to indemnify Oracle from and against any 
and all actions, claims, losses, damages, liabilities and expenses (including reasonable attorneys' fees) 
arising out of Partner’s use of the Materials.
5. Reference materials including but not limited to those identified in the Boot Camp manifest can not be 
redistributed in any format without Oracle written consent.
Oracle Training Materials – Usage 
Agreement
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Key Content Contributors
Contributing Companies
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Boot Camp Objectives
• Overview of Oracle Inventory
• Inventory Structure
• Units of Measure
• Defining and Maintaining Items
• Lot and Serial Control
• Transaction Setup
• Issuing and Transferring Material
• On-hand and Availability
• Material Status Control
• Inventory Replenishment
• Inventory Accuracy
• Table Information
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Session Objectives
• Identify the Oracle Inventory tables
• Access eTRM
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Inventory Table Inforamtion
• Organization tables
• Sub inventories and Locator Tables
• Item Master Tables
• Item Status tables
• Item Categories Tables
• Item Reservation Tables
• Inventory Transaction Tables
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Organization Table
• MTL_PARAMETERS: Associated with an organization. Primary 
key:ORGANIZATION_ID
• MTL_INTERORG_PARAMETERS: Identifies the receiving organizations linked to a 
particular organization. You must define the interorganizational relationship before you 
perform any interorganization transfers. Units of measure, distance, transfer charge, and 
accounting information are also specified in this table. Primary 
key:FROM_ORGANIZATION_ID, TO_ORGANIZATION_ID
• GL_SETS_OF_BOOKS: Stores define set of books data in General Ledger. Each row 
includes the set of books name, description, functional currency, and other data 
Corresponds to the Define Set of Books window. Primary key: 
GENERAL_LEDGER_UPDATE_CODE
• GL_CODE_COMBINATIONS: Stores valid Accounting Flexfield segment value 
combinations for each accounting flexfield structure within your General Ledger. Primary 
key: CODE_COMBINATION_ID
• HR_ALL_ORGANIZATION_UNITS: Stores generic data about organizations. An
• organization can be used for various purposes in Oracle Manufacturing applications. For 
example, one organization can represent a business group, an ECO department, a 
Planning organization, and an Inventory organization. Primary key: ORGANIZATION_ID
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Subinventories and Locator Table
• MTL_SECONDARY_INVENTORIES: This table is the definition table for the 
subinventory. Subinventories are assigned to items, indicating a list of valid places in 
which this item may be located. The primary key is SECONDARY_INVENTORY_NAME, 
ORGANIZATION_ID 
• MTL_ITEM_SUB_INVENTORIES: This table maintains a listing of subinventories
assigned to an inventory or engineering item. The subinventories make up the valid list 
of subinventories when transacting a specific item if the user has specified that the item 
must use subinventories from a predefined list. The primary key is 
INVENTORY_ITEM_ID, ORGANIZATION_ID, SECONDARY_INVENTORY
• MTL_ITEM_LOCATIONS: This table is the definition table for stock locators. The 
associated attributes describe the subinventory to which this locator belongs, and the 
locator physical capacity. The primary key is INVENTORY_LOCATION_ID 
• MTL_SECONDARY_LOCATORS: This table stores all locators within a subinventory for 
a specific item. The locators make up the valid list of locators when transacting that 
specific item to/from the subinventory when the user has specified that the item must use 
locators from a predefined list. The primary key is INVENTORY_ITEM_ID, 
ORGANIZATION_ID, SECONDARY_LOCATORS
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Item Master Table
• MTL_SYSTEM_ITEMS_B: This table is the definition table for items. This table holds the definitions for 
inventory items, engineering items, and purchasing items. The flexfield code is MSTK. The primary key for 
an item is the INVENTORY_ITEM_ID and the ORGANIZATION_ID. The same item could be defined in 
more than one organization.Each row represents in item in an organization
• MTL_ITEM_ATTRIBUTES: This table stores the item attributes, their user-friendly names if the item is 
maintained at the item or item/organization level, and the kind of validation required for each attribute. This 
table is seeded on install or upgrade. The primary key is ATTRIBUTE_NAME 
• MTL_ITEM_LOCATIONS:This table is the definition table for stock locators. The associated attributes 
describe the subinventory to which this locator belongs, and the locator physical capacity. The primary key is 
INVENTORY_LOCATION_ID 
• MTL_ITEM_TEMPL_ATTRIBUTES: This table stores the attributes and attribute values for a template. 
When a template is created, a row is inserted for each available item attribute. When the template is applied 
to an item, the enabled attribute values are propagated to the item. The primary key is TEMPLATE_ID, 
ATTRIBUTE_NAME
• MTL_ITEM_STATUS_TL: This table is the definition table for a material status code. The status code is a 
required item attribute and indicates the item status (for example, active, pending, or obsolete). You may 
define as many additional status codes as you want. The values of the individual status attributes associated 
with an item status are stored in MTL_STATUS_ATTRIBUTE_VALUES. The primary key is 
INVENTORY_ITEM_STATUS_CODE
• MTL_ITEM_TEMPLATES_B: This table is the definition table for item templates. It contains the user-
defined name and description. When the template is applied to an item, the enabled item attributes are 
propagated to the item. The primary key is TEMPLATE_ID
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Item Status Table
• MTL_SYSTEM_ITEMS_B: This table is the definition table for items. This table holds the 
definitions for inventory items, engineering items, and purchasing items. The flexfield code is 
MSTK. The primary key for an item is the INVENTORY_ITEM_ID and the 
ORGANIZATION_ID. The same item could be defined in more than one organization. Each 
row represents an item in an organization
• MTL_ITEM_ATTRIBUTES: This table stores the item attributes, their user-friendly names if 
the attribute is maintained at the item or item/organization level, if the attribute is controlled 
by an item’s status, and the kind of validation required for each attribute. The primary key is 
ATTRIBUTE_NAME. This table is seeded on install or upgrade. The top eight item attributes 
are as follows:
• - Stockable
• - Transactable
• - BOM allowed
• - WIP allowed
• - Purchasable
• - OE Orderable
• - Internal Orderable
• - Billable
• MTL_STATUS_ATTRIBUTE_VALUES: This table stores the attribute values associated with 
an item status code. One record is created for each of the function-enabling attributes for 
each defined item status code. The primary key is INVENTORY_ITEM_STATUS_CODE, 
ATTRIBUTE_NAME
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Item Categories Table
• FND_ID_FLEX_STRUCTURES: This table stores structure information about key flexfields. 
The primary key is APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM.
• MTL_CATEGORIES_B: This table is the code combinations table for item categories. Items 
are grouped into categories within the context of a category set to provide flexible grouping 
schemes. The primary key is CATEGORY_ID. The item category is a key flexfield with a 
flexfield code of MCAT
• MTL_CATEGORIES_TL: This table is a table holding translated Description column for
• Item Categories. The primary key is CATEGORY_ID, LANGUAGE
• MTL_CATEGORY_SETS_B: This table contains the entity definition for category sets. A 
category set is a categorization scheme for a group of items. Items can be assigned to 
different categories in different category sets, but can be assigned to only one category 
within a category set. The primary key is CATEGORY_SET_ID
• MTL_CATEGORY_SETS_TL: This table is a table holding translated Name and Description 
columns for Category Sets. The primary key is CATEGORY_SET_ID, LANGUAGE
• MTL_CATEGORY_SET_VALID_CATS: This table defines the valid category list for a 
particular category set. If category validation is required, the list of valid categories is stored 
in this table and is used to restrict category selection for the category set. The primary key is 
CATEGORY_SET_ID, CATEGORY_ID
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Item Reservation Table
• MTL_DEMAND: This table stores demand and reservation information used in Available to 
Promise, Planning, and other manufacturing functions. Four row types are stored in this 
table:
• -Summary Demand rows
• - Dependent Demand rows
• -Open Demand rows
• - Reservation rows
The primary key is INVENTORY_ITEM_ID, DEMAND_SOURCE_TYPE, 
DEMAND_SOURCE_HEADER_ID, DEMAND_SOURCE_LINE, DEMAND_SOURCE_DELIVERY, 
COMPONENT_SEQUENCE_ID
• MTL_SALES_ORDERS: This table stores the Oracle Inventory local definition of sales 
orders and maps sales orders between Oracle Inventory and other Oracle Manufacturing 
applications. The primary key is SALES_ORDER_ID
• OE_ORDER_HEADERS_ALL: This table stores header information for orders in Oracle 
Order Management. The primary key is HEADER_ID.
• OE-ORDER_LINES_ALL: This table stores information for all order lines in Oracle Order 
Management. The primary key is LINE_ID
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Oracle Inventory Tables
Item Transaction Table
• MTL_TRANSACTIONS_INTERFACE: This is the interface point between non-Inventory 
applications and the Inventory transaction module. The Transaction Manager concurrent 
program polls this table at a user-specified process interval and submits the transaction 
workers to process them. Processing consists of data derivation, validation, and transfer of 
records from 
MTL_TRANSACTIONS_INTERFACE,MTL_TRANSACTION_LOTS_INTERFACE, and 
MTL_SERIAL_NUMBERS_INTERFACE into their respective TEMP tables, from where they 
are processed by the transaction processor.
• MTL_MATERIAL_TRANSACTIONS_TEMP: This table is the gateway for all material 
transactions. Records are processed from this table into Inventory through the transaction 
processor. All Inventory transaction forms write directly to this table. Outside applications 
must write transaction records to MTL_TRANSACTSIONS_INTERFACE TXN Processor 
makes use of TXN worker to process the transactions from 
MTL_TRANSACTIONS_INTERFACE to MTL_MATERIAL_TRANSACTIONS_TEMP and 
from MTL_MATERIAL_TRANSACTIONS_TE MP to MTL_MATERIAL_TRANSACTIONS..
• MTL_MATERIAL_TRANSACTIONS: This table stores a record of every material transaction 
or cost update performed in Inventory. Records are inserted into this table either through the 
transaction processor or by the standard cost program. The primary key is 
TRANSACTION_ID
• MTL_TRANSACTION_ACCOUNTS: This table holds the accounting information for each 
material transaction in MTL_MATERIAL_TRANSACTIONS. Oracle Inventory uses this 
information to track the financial impact of your quantity moves
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Session Objectives
• Identify the Oracle Inventory tables
• Access eTRM
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Access eTRM
Electronic Technical Reference Manual (eTRM): It’s a pl/sql utility 
that reads design information in an Oracle database and displays 
its output in html format. It shows database design and 
dependency information for the Oracle eBusiness Suite.
Navigation Details…
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
URL: http://etrm.oracle.com
On clicking the FND Datamentioned in the previous page, 
the page above opens with a search option on the right 
side. Similarly, DBA data, HTML and PDF files can be 
opened for all Oracle products.
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Navigation details…
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential 
Summary
• In this lesson, you should have learned about:
• Important Inventory Tables
• How to access Electronic Technical Reference Manual (eTRM)
For Oracle employees and authorized partners only. Do not distribute to third parties. 
© 2011 Oracle Corporation – Proprietary and Confidential