EBS Reference

MTL_SECONDARY_INVENTORIES

Schema: INVmasterPer inventory org

The subinventory master — each row a named section of stock within an organization (stores, staging, WIP, rejects) with its asset/expense nature, tracking and reservability controls, and default accounts

Module: InventoryInventory-org partitioned (ORGANIZATION_ID)

Notes

The name column is only 10 characters (SECONDARY_INVENTORY_NAME, VARCHAR2(10)) — size landed columns accordingly.

What the badges mean

master: Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or an APPS-schema view.

Fields

10 fields · 2 key

Table fields: position, field name, description, data type, and flags. 10 fields.
#	Field	Description	Type	Flags
1	SECONDARY_INVENTORY_NAME	The subinventory code (10 characters max) balances and transactions carry	VARCHAR2	Key
2	ORGANIZATION_ID	Inventory organization — subinventory names are unique per org, not globally	NUMBER	Key
3	DESCRIPTION	Subinventory description	VARCHAR2
4	AVAILABILITY_TYPE	Whether the subinventory's stock nets into available-to-promise	NUMBER
5	RESERVABLE_TYPE	Whether stock here can be reserved	NUMBER
6	LOCATOR_TYPE	Locator control for the subinventory (none, prespecified, dynamic)	NUMBER
7	ASSET_INVENTORY	Asset vs expense subinventory — expense subinventories don't carry inventory value	NUMBER
8	QUANTITY_TRACKED	Whether on-hand is tracked here at all	NUMBER
9	DISABLE_DATE	When the subinventory was disabled — NULL while active	DATE
10	MATERIAL_ACCOUNT	The GL account combination (CCID) material value in this subinventory posts to	NUMBER

Field provenance: hand-curated. 2 key fields.

Boilerplate SQL

Starting point for reading MTL_SECONDARY_INVENTORIESon Databricks — real DATE columns need no conversion, and the org anchor and the LAST_UPDATE_DATE watermark are already in place. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.

Query parameters

CatalogSchemaInventory org (ORGANIZATION_ID)Subinventory namewatermark

Include optional filters (2)

-- ============================================================
-- Table  : MTL_SECONDARY_INVENTORIES — The subinventory master — each row a named section of stock within an organization (stores, staging, WIP, rejects) with its asset/expense nature, tracking and reservability controls, and default accounts
-- Purpose: Column-selected read of MTL_SECONDARY_INVENTORIES — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + SECONDARY_INVENTORY_NAME
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.SECONDARY_INVENTORY_NAME AS "The subinventory code (10 characters max) balances and transactions carry",
  t.ORGANIZATION_ID AS "Inventory organization — subinventory names are unique per org, not globally",
  t.DESCRIPTION AS "Subinventory description",
  t.AVAILABILITY_TYPE AS "Whether the subinventory's stock nets into available-to-promise",
  t.RESERVABLE_TYPE AS "Whether stock here can be reserved",
  t.LOCATOR_TYPE AS "Locator control for the subinventory (none, prespecified, dynamic)",
  t.ASSET_INVENTORY AS "Asset vs expense subinventory — expense subinventories don't carry inventory value",
  t.QUANTITY_TRACKED AS "Whether on-hand is tracked here at all",
  t.DISABLE_DATE AS "When the subinventory was disabled — NULL while active",
  t.MATERIAL_ACCOUNT AS "The GL account combination (CCID) material value in this subinventory posts to"
FROM <catalog>.<schema>.MTL_SECONDARY_INVENTORIES t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.SECONDARY_INVENTORY_NAME = '<SECONDARY_INVENTORY_NAME>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.SECONDARY_INVENTORY_NAME;

5 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <SECONDARY_INVENTORY_NAME>, <watermark>

Relationships

1-hop neighbors — click a table to navigate there. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.

Join details

MTL_SECONDARY_INVENTORIESMTL_PARAMETERSforeign key · N:1
ON MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID
MTL_ITEM_LOCATIONSMTL_SECONDARY_INVENTORIESforeign key · N:1
ON MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE = MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME AND MTL_ITEM_LOCATIONS.ORGANIZATION_ID = MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID
MTL_ONHAND_QUANTITIES_DETAILMTL_SECONDARY_INVENTORIESforeign key · N:1
ON MTL_ONHAND_QUANTITIES_DETAIL.SUBINVENTORY_CODE = MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME AND MTL_ONHAND_QUANTITIES_DETAIL.ORGANIZATION_ID = MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONSMTL_SECONDARY_INVENTORIESforeign key · N:1
ON MTL_MATERIAL_TRANSACTIONS.SUBINVENTORY_CODE = MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME AND MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID = MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID

Browse more Inventorytables →