Results for “subinventory_id”

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

Overview

WMS_DEPARTMENT_SUBINVENTORIES is a Warehouse Management (WMS) schema table that functions as a cross-reference between organizational departments and inventory subinventories. In Oracle EBS 12.1.1 and 12.2.2, it enables the association of a department (defined in BOM_DEPARTMENTS) with a specific subinventory within an inventory organization (defined in MTL_PARAMETERS). This relationship is typically used to support picking, replenishment, and material issue rules where departmental ownership or consumption of a given subinventory must be enforced or reported.

The table holds 27 documented columns, including a standard Oracle EBS audit trail (WHO columns), effective dating fields (EFFECTIVE_FROM_DATE, EFFECTIVE_TO_DATE), and 15 descriptive flexfield (DFF) attribute columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15). The primary key WMS_DEPARTMENT_SUBINVENTORI_PK is composed of ORGANIZATION_ID, SUBINVENTORY_CODE, and DEPARTMENT_ID, ensuring that a department–subinventory association is unique within an organization. Based on the foreign key structure mining, the heuristic Data Vault classification is link, which is appropriate since this table resolves a many-to-many relationship between departments and subinventories rather than holding descriptive attributes of a single business entity.

Key Information Stored

• ORGANIZATION_ID — The inventory organization identifier; part of the primary key and foreign key to MTL_PARAMETERS.
• SUBINVENTORY_CODE — The subinventory name/code; part of the primary key and foreign key to MTL_SECONDARY_INVENTORIES.
• SUBINVENTORY_ID — Surrogate identifier for the subinventory, stored for efficient joins.
• DEPARTMENT_ID — The department identifier; part of the primary key and foreign key to BOM_DEPARTMENTS.
• EFFECTIVE_FROM_DATE / EFFECTIVE_TO_DATE — Date range during which the department–subinventory association is active.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing creation and modification metadata.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield columns available for customer-specific extensions.

The unique index WMS_DEPARTMENT_SUBINVENTORI_U1 (ORGANIZATION_ID, SUBINVENTORY_CODE, DEPARTMENT_ID) mirrors the primary key and represents the business-key candidate for this link table. Applications should treat this combination as the natural key when performing upserts or deduplication.

Common Use Cases and Queries

Typical use cases include validating whether a department is permitted to transact against a given subinventory, driving default subinventory selection on departmental material issues, and reporting on departmental inventory consumption. A representative query joins the link table to MTL_SECONDARY_INVENTORIES and BOM_DEPARTMENTS:

• SELECT ds.ORGANIZATION_ID, ds.SUBINVENTORY_CODE, bd.DEPARTMENT_CODE, bd.DESCRIPTION FROM WMS.WMS_DEPARTMENT_SUBINVENTORIES ds JOIN BOM.BOM_DEPARTMENTS bd ON bd.DEPARTMENT_ID = ds.DEPARTMENT_ID JOIN INV.MTL_SECONDARY_INVENTORIES msi ON msi.ORGANIZATION_ID = ds.ORGANIZATION_ID AND msi.SECONDARY_INVENTORY_NAME = ds.SUBINVENTORY_CODE WHERE ds.ORGANIZATION_ID = :org_id AND ds.EFFECTIVE_FROM_DATE <= SYSDATE AND (ds.EFFECTIVE_TO_DATE IS NULL OR ds.EFFECTIVE_TO_DATE >= SYSDATE;
• Reporting the count of active subinventories per department for capacity planning.
• Detecting orphaned or expired associations where EFFECTIVE_TO_DATE has passed but downstream logic still references the row.

Related Objects

• MTL_SECONDARY_INVENTORIES — joined on ORGANIZATION_ID and SUBINVENTORY_CODE to obtain subinventory attributes.
• BOM_DEPARTMENTS — joined on DEPARTMENT_ID to resolve department name and code.
• MTL_PARAMETERS — joined on ORGANIZATION_ID to validate inventory organization context.
• WMS_DEPARTMENT_SUBINVENTORIES (self) — via the unique index WMS_DEPARTMENT_SUBINVENTORI_U1 for deduplication checks.
• Related WHO audit views and DFF definition tables (FND_DESCR_FLEX_COLUMN_USAGES) that expose the ATTRIBUTE columns.

Because the table is classified as a link, it should be treated as dependent on its parent entities; deletes or changes to MTL_SECONDARY_INVENTORIES, BOM_DEPARTMENTS, or MTL_PARAMETERS must respect the foreign key constraints defined on this table.