oracle_inventory

1. Organization access screen enable users to check if a Responsibility can access the Organization or not.

2. Following are tables for inventory with description.

nventory related Queries

Inventory Lerated Queries

INVENTORY RELATED QUERIES
--- get item attributes NOT UNDER status control
SELECT   meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1,
-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM     mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/

-- get item status attribute controls

SELECT   ia.attribute_group_id GROUP_ID, ia.user_attribute_name_gui,
lk.meaning controlled_at, ia.attribute_name,
--   ia.user_attribute_name,
ia.status_control_code,
ia.validation_code
FROM fnd_lookup_values lk, mtl_item_attributes ia
WHERE ia.control_level = lk.lookup_code
AND lk.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'
ORDER BY ia.attribute_group_id, 1
/

-- find item status attributes :

SELECT   mis.inventory_item_status_code item_status, mis.description,
mis.disable_date, av.attribute_name, av.attribute_value VALUE
FROM mtl_item_status mis, mtl_status_attribute_values av
WHERE mis.inventory_item_status_code = av.inventory_item_status_code
ORDER BY 1
/
-- get item attributes UNDER status control :

SELECT   meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM     mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NOT NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/

--- find an Item attribute info :

SELECT   segment1 item, msi.description, inventory_item_id,
ml.meaning item_type,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_status_code
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_status_code')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.shippable_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.shippable_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.mtl_transactions_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.mtl_transactions_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.so_transactions_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.so_transactions_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.customer_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.customer_order_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_asset_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_asset_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.eng_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name
|| '.'
|| msi.service_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.service_item_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.build_in_wip_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.build_in_wip_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.bom_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.bom_enabled_flag')
ATTRIBUTE,
(SELECT    ia.user_attribute_name_gui
|| '.'
|| msi.stock_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.stock_enabled_flag')
ATTRIBUTE
FROM fnd_lookup_values ml, mtl_system_items msi
WHERE msi.segment1 LIKE 'AS18947%'
AND msi.organization_id = 204
AND msi.item_type = ml.lookup_code(+)
AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY 1, 2
/

--- find Item template attribute VALUES :

SELECT   it.template_name, ita.attribute_name, ita.attribute_value
FROM mtl_item_templates it, mtl_item_templ_attributes ita
WHERE it.template_name LIKE 'xxx%'
AND it.template_id = ita.template_id
AND ita.attribute_value IS NOT NULL
ORDER BY 1, 2
/

--- find  item cross-REFERENCES :


/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT   msi.segment1 item, mcr.cross_reference_type reference_type,
mcr.cross_reference, mcr.description
FROM mtl_cross_references mcr, mtl_system_items msi
WHERE mcr.cross_reference_type = 'Vendor'
AND mcr.inventory_item_id = msi.inventory_item_id
AND mcr.organization_id = msi.organization_id
ORDER BY 1, 2
/


-- find Customer items :


/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT   hp.party_name customer, ci.customer_item_number,
ci.customer_item_desc, msi.segment1 item, msi.description item_desc,
ci.customer_category_code, ci.item_definition_level,
ci.commodity_code_id, ci.address_id
FROM hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
WHERE ci.customer_item_id = ix.customer_item_id
AND ix.inventory_item_id = msi.inventory_item_id
AND ix.master_organization_id = msi.organization_id
AND ci.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
ORDER BY 1, 2
/

---find Manufacturer items :


SELECT   mm.manufacturer_name, mp.mfg_part_num, mp.description,
msi.segment1 inv_item, msi.description item_desc
FROM mtl_system_items msi, mtl_mfg_part_numbers mp, mtl_manufacturers mm
WHERE mm.manufacturer_id = mp.manufacturer_id
AND mp.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = msi.organization_id
ORDER BY 1, 2
/

--find related items :

SELECT   ito.segment1 item, ito.description, itr.segment1 related_item,
itr.description, ml.meaning relation, ri.reciprocal_flag
FROM mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
WHERE ri.inventory_item_id = ito.inventory_item_id
AND ri.organization_id = ito.organization_id
AND ri.related_item_id = itr.inventory_item_id
AND ri.organization_id = itr.organization_id
AND ri.relationship_type_id = ml.lookup_code
AND ml.lookup_type(+) = 'MTL_RELATIONSHIP_TYPES'
ORDER BY 1, 2
/
-- find DEFAULT category FOR a category SET :

/* Formatted on 2010/08/24 11:28 (Formatter Plus v4.8.0) */
SELECT   mcats.category_set_name, mcat.segment1 default_category,
mcat.description cat_desc, mcat.category_id, mcats.category_set_id
FROM mtl_category_sets mcats, mtl_categories mcat
WHERE mcats.category_set_name LIKE '%'
AND mcat.category_id = mcats.default_category_id
ORDER BY 1, 2
/

-- find ALL items assigned TO categories OF a category SET :

SELECT   mcats.category_set_name,
mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,
msi.description item_desc
FROM mtl_item_categories micat,
mtl_category_sets mcats,
mtl_categories mcat,
mtl_system_items_vl msi
WHERE mcats.category_set_name LIKE 'Inv%'
AND micat.category_set_id = mcats.category_set_id
AND micat.category_id = mcat.category_id
AND mcat.segment1 LIKE 'N%'
AND msi.inventory_item_id = micat.inventory_item_id
AND msi.organization_id = micat.organization_id
AND msi.organization_id = 204
ORDER BY 1, 2, 3
/

 

Copied from
 http://imdjkoch.wordpress.com/2011/07/16/key-tables-in-oracle-inventory/

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.