Inventory KEY Tables

Oracle Apps Inventory KEY Tables:
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 relationships between 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 sub inventory, 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 thecategory 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:
One rcv_shipment_header record
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:
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).