Oracle Apps Inventory KEY Tables

Table	Description
MTL_PARAMETERS	It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.
MTL_SYSTEM_ITEMS_B	This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages.
MTL_ITEM_STATUS	This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.
MTL_UNITS_OF_MEASURE_TL	This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.
MTL_ITEM_LOCATIONS	This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
MTL_ITEM_CATEGORIES	This table stores inventory item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items always may be assigned to multiple category sets. However, depending on the Multiple Assignments Allowed attribute value in a given category set definition, an item can be assigned to either many or only one category in that category set.
MTL_CATEGORIES_B	This 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. Item categories now support multilingual category description. MLS is implemented with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.
MTL_CATEGORY_SETS_B	It contains the entity definition for category sets. A category set is a categorization scheme for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes. An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL table holds translated Name and Description for Category Sets.
MTL_DEMAND	This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows, Open Demand Rows, and Reservation Rows.
MTL_SECONDARY_INVENTORIES	This is the definition table for the subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory.
MTL_ONHAND_QUANTITIES	It stores quantity on hand information by control level and location. It is maintained as a stack of receipt records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria.
MTL_TRANSACTION_TYPES	It contains seeded transaction types and the user defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is associated with each transaction type.
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 update program. The columns TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe what the transaction is and against what entity it was performed.
MTL_ITEM_ATTRIBUTES	This table stores information on item attributes. Each row in the table corresponds to an attribute. The table stores the attribute name, the corresponding user-friendly name seen by the users, and the kind of validation enforced on the attribute.
MTL_ITEM_CATALOG_GROUPS_B	This is the code combinations table for item catalog groups. An item catalog group consists of items that can be described by the same set of descriptive elements or item properties. When an item is associated with an item catalog group, the item inherits the descriptive elements for that group which then behave like additional item attributes.
MTL_ITEM_REVISIONS_B	It stores revision levels for an inventory item. When an item is defined a starting revision record is written out to this table, so every item will at least have one starting revision.
MTL_ITEM_TEMPLATES_B	This is the definition table for item templates. It contains the user-defined name (TEMPLATE_NAME) and description (DESCRIPTION) ONLY for backward compatibility. You can use a template to set certain item attributes.
MTL_DESCRIPTIVE_ELEMENTS	It stores the descriptive element definitions for an item catalog group. Descriptive elements are defining properties used to describe in the catalog group.
MTL_DESCR_ELEMENT_VALUES	It stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
ORG_ACCT_PERIODS	It holds the open and closed financial periods for organizations.
MTL_CUSTOMER_ITEMS	It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.
MTL_SYSTEM_ITEMS_INTERFACE	It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.
MTL_TRANSACTIONS_INTERFACE	It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.
MTL_ITEM_REVISIONS_INTERFACE	It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.
MTL_ITEM_CATEGORIES_INTERFACE	This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.
MTL_DESC_ELEM_VAL_INTERFACE	This table temporarily stores descriptive element values for an item that is associated with an item catalog group before loading this information into Oracle Inventory.
MTL_DEMAND_INTERFACE	It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.
MTL_INTERFACE_ERRORS	It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

mtl_system_items
mtl_item_revisions
mtl_item_categories
mtl_material_transactions
mtl_transaction_lots_numbers
mtl_serial_numbers
rcv_shipment_headers
rcv_shipment_lines
mtl_system_item_interface
mtl_item_revisions_interface
mtl_item_categories_interface
mtl_transaction_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface
rcv_headers_interface
rcv_transactions_interface

MTL_SYSTEM_ITEMS_B

MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item–related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing.

You can set up the item with multiple segments, since it is implemented as a flexfield. Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.

Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationshipsbetween item attributes and Oracle applications.

Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table. PRIMARY_UOM_CODE is the 3–character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25–character unit that is used throughout Oracle Purchasing.

Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only. "

MTL_DEMAND

This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows, Open Demand Rows, and Reservation Rows.

Summary Demand is direct demand for an item within an organization a particular date that originated from a particular source. For hard reservations there are several columns which further define what the reservation is for, and where it is being placed. Currently, four sources of demand are supported, Sales Order, Account, Account Alias, and User Defined transaction sources.

Five different types of demand, denoted by DEMAND_TYPE column, are used. These five types are Model, Option Class, Option Item, Configuration Item and Derived. Derived demand rows are inserted by BOM Demand exploder when demanded item has ATPable components.

Each Summary Demand row may be associated with one or more Reservation rows. Reservation may be placed against a particular inventory control (that is, specific subinventory, locator, revision and lot) against any sources (that is, Account Number, Account Alias, Sales Order or even User–Defined sources).

Each Summary Demand row may be associated with one or more detailed rows. The detailed rows consist of reservations and open demand. A reservation row represents a firm promise of a supply source. Currently, two types of reservation are supported, reservations to on–hand, and reservations to WIP jobs.

Each summary demand row may be associated with one and only one open demand row. Open Demand rows represent the un–reserved portion of the the Summary Demand.

MTL_CATEGORIES_B

MTL_CATEGORIES_B 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 item category is a key flexfield with a flex code of MCAT. The flexfield structure identifier is also stored in this table.

MTL_CATEGORY_SETS_B

MTL_CATEGORY_SETS_B contains the entity definition for category sets. A category set is a categorization scheme for a group of items.

Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.
An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the
category set. Only categories with the same flexfield structure may be grouped into a category set.

CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned. VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.

MTL_ITEM_CATEGORIES

MTL_ITEM_CATEGORIES stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization Items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.

MTL_ITEM_SUB_INVENTORIES

MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories assigned to an inventory or engineering item. These subinventories make up the list of valid subinventories when transacting this specific item and the user has specified (in the master window) that the item must use subinventories restricted to a pre–defined list.

RCV_TRANSACTIONS

It stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table. Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.

RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES

When a Ship Confirm is processed, one record is inserted in rcv_shipment_headers and one record is inserted in rcv_shipment_lines for each of the Sales Order Lines. The rcv_shipment_lines are linked to the one rcv_shipment_header record by shipment_header_id.

When a Shipment Line is received, the Receipt Number is populated in the rcv_shipment_headers record that was created for that Shipment. Since only one rcv_shipment_headers record is created for each Ship Confirm process and the Receipt Number is also on rcv_shipment_headers record, there can only be one Receipt Number for a specific Shipment.

For example:

1. Ship Confirm Sales Order Lines 1, 2, 3, 4, 5

a. The following records are created:

o                  One rcv_shipment_header record

o                  Five rcv_shipment_lines records

b. rcv_shipment_lines.shipment_header_id will be the same for all five records, which will also be the same value as rcv_shipment_header.shipment_header_id

2. Receive one Shipment Line:

o                  rcv_shipment_headers record will be updated with the Receipt Number

3. Subsequent Receiving transactions will reference the same rcv_shipment_header record (therefore, there can only be one Receipt Number).