Results for “planning_level”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_SECONDARY_INVENTORIES is the master definition table for subinventories in Oracle E-Business Suite Inventory (INV). Each row represents a single subinventory within a specific inventory organization, and stores the operational, accounting, planning, and material-handling attributes that govern how material is received, stored, picked, transferred, and costed within that subinventory. Because practically every inventory transaction, reservation, move order, and on-hand quantity carries a subinventory reference, this table functions as a foundational reference entity across INV, WIP, PO, OM, RCV, MRP, and Warehouse Management modules.

From a Data Vault modeling perspective, the heuristic classification of this table is hub-leaning. It holds a stable, well-defined business key (the subinventory name scoped to an organization) and is referenced by an exceptionally large number of downstream transaction tables, which is characteristic of a hub rather than a transactional satellite. A Data Vault design would likely model MTL_SECONDARY_INVENTORIES as a hub keyed on ORGANIZATION_ID plus SECONDARY_INVENTORY_NAME, with one or more satellites capturing descriptive and accounting attributes that change over time.

Key Information Stored

The documented physical schema contains 75 columns. The primary key constraint is MTL_SECONDARY_INVENTORIES_PK, defined on (SECONDARY_INVENTORY_NAME, ORGANIZATION_ID). Note that the two columns together, rather than a single surrogate ID, form the key, which reflects that subinventory names are only unique within an organization. A separate unique index, MTL_SECONDARY_INVENTORIES_U1, is defined on (ORGANIZATION_ID, SECONDARY_INVENTORY_NAME), which is the same business-key combination and confirms that this pair is the natural business key rather than a generated surrogate.

The most operationally significant columns include:

• SECONDARY_INVENTORY_NAME and ORGANIZATION_ID — the composite primary key and business identifier, defining the subinventory within its inventory organization.
• DESCRIPTION and DISABLE_DATE — descriptive text and the date on which the subinventory became inactive.
• AVAILABILITY_TYPE, RESERVABLE_TYPE, and INVENTORY_ATP_CODE — control whether on-hand balances are available to promise, reservable, and included in ATP calculations.
• LOCATOR_TYPE and QUANTITY_TRACKED — determine whether locators and quantity tracking are enforced for the subinventory.
• MATERIAL_ACCOUNT, MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT, OVERHEAD_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT, EXPENSE_ACCOUNT, and ENCUMBRANCE_ACCOUNT — GL account references that drive subinventory accounting and cost collection.
• SOURCE_TYPE, SOURCE_SUBINVENTORY, and SOURCE_ORGANIZATION_ID — define replenishment sourcing from another subinventory or organization.
• STATUS_ID and DEFAULT_LOC_STATUS_ID — link to material status controls applied to the subinventory.
• DEFAULT_COST_GROUP_ID — associates the subinventory with a default cost group for costing purposes.
• PICK_UOM_CODE, PICK_METHODOLOGY, PICKING_ORDER, and DROPPING_ORDER — govern picking behavior and sequencing.
• SUBINVENTORY_TYPE, SUBINVENTORY_USAGE, PLANNING_LEVEL, and DEMAND_CLASS — classify the subinventory for planning, MRP, and demand segregation.

Common Use Cases and Queries

The most common reporting requirement is to resolve subinventory attributes for on-hand, transaction, or replenishment analysis. For example, listing active subinventories for an organization:

SELECT secondary_inventory_name, description, availability_type, locator_type FROM mtl_secondary_inventories WHERE organization_id = :org_id AND disable_date IS NULL ORDER BY secondary_inventory_name;

Joining to on-hand quantities to summarize inventory by subinventory is equally frequent, joining MTL_ONHAND_QUANTITIES.SUBINVENTORY_CODE to SECONDARY_INVENTORY_NAME on matching ORGANIZATION_ID. Because the primary key couples the name with organization, every join must include ORGANIZATION_ID to avoid cross-organization row duplication. Other typical scenarios include validating which subinventories are reservable or ATP-enabled before committing a transaction, retrieving the accounting flexfield assignments for cost accounting reconciliations, and identifying replenishment sources by tracing SOURCE_SUBINVENTORY and SOURCE_ORGANIZATION_ID.

Related Objects

This table is a highly referenced hub. The most significant related objects, using the documented join columns, include:

• MTL_ONHAND_QUANTITIES and MTL_ONHAND_QUANTITY_TEMP — join via SUBINVENTORY_CODE and ORGANIZATION_ID; the primary source of on-hand balances per subinventory.
• MTL_MATERIAL_TRANSACTIONS and MTL_TRANSACTIONS_INTERFACE — join via SUBINVENTORY_CODE (and TRANSFER_SUBINVENTORY in the interface); record all material movements.
• MTL_PARAMETERS — both a parent (ORGANIZATION_ID) and a referencing table (SOURCE_SUBINVENTORY), tying subinventories to organization parameters.
• MTL_ITEM_LOCATIONS and MTL_ITEM_SUB_INVENTORIES — join via SUBINVENTORY_CODE / SECONDARY_INVENTORY, defining locators and item-level subinventory restrictions.
• MTL_RESERVATIONS and MTL_DEMAND — join via SUBINVENTORY_CODE / SUBINVENTORY, reflecting reservation and demand flow.
• MTL_TXN_REQUEST_HEADERS and MTL_TXN_REQUEST_LINES — join via FROM_SUBINVENTORY_CODE and TO_SUBINVENTORY_CODE, supporting move order processing.
• WIP_DISCRETE_JOBS, WIP_REPETITIVE_ITEMS, and BOM_OPERATIONAL_ROUTINGS — join via COMPLETION_SUBINVENTORY, driving manufacturing completion and supply.
• RCV_SHIPMENT_LINES and RCV_TRANSACTIONS — join via TO_SUBINVENTORY / SUBINVENTORY, linking receiving activity to destination subinventories.
• GL_CODE_COMBINATIONS — referenced by the multiple accounting columns (MATERIAL_ACCOUNT, EXPENSE_ACCOUNT, ENCUMBRANCE_ACCOUNT, and others) for financial posting.
• MTL_MATERIAL_STATUSES_B and CST_COST_GROUPS — referenced via STATUS_ID and DEFAULT_COST_GROUP_ID, controlling status and costing defaults.