Results for “edw_mtl_ildm_sub_inv_ltc”

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

Overview

The EDW_MTL_ILDM_SUB_INV_LTC table is a level (dimension) table within the Oracle E-Business Suite Operations Intelligence (OPI) product, owned by the OPI schema. It represents the SUB_INV (sub-inventory) level of the ILDM (Inventory Locator Dimension) — the abstraction hierarchy used by OPI's warehousing and analytics layer to describe where material resides within an organization, from inventory organization through to the finest sub-inventory grain. In the EBS 12.1.1 and 12.2.2 releases, this object is documented as Obsolete, meaning it persists as a valid but legacy artifact, typically retained for backward compatibility with pre-existing OPI/ETRM data models and extract structures rather than for new development.

The heuristic Data Vault classification mined from the key structure is standalone — no enforced foreign-key relationships to parent hub or link tables were detected. As a modeling suggestion, this table behaves less like a classical hub or link and more like a satellite/level (dimension) table: it carries descriptive attributes and business keys for the sub-inventory dimension, keyed by surrogate identifiers. Its role is to enumerate and label sub-inventory records so that fact-style inventory and transaction data can be sliced by sub-inventory for reporting. Since OPI is designated obsolete, this object should be treated as reference-only when maintained on modern configurations.

Key Information Stored

Only 15 columns are documented for this table. The most significant are described below; the remainder are standard audit and extensibility fields.

• STOCK_ROOM_PK — the surrogate primary key of the level table, enforced by the unique index EDW_MTL_ILDM_SUB_INV_LTC_U. This is the identifier the OPI dimension exposes to downstream facts.
• STOCK_ROOM_PK_KEY — the business-key candidate, enforced by the unique index EDW_MTL_ILDM_SUB_INV_LTC_UKEY. Distinguishing the surrogate (STOCK_ROOM_PK) from the business key (STOCK_ROOM_PK_KEY) allows facts to reference a stable internal identifier while business users reason over the natural key.
• STOCK_ROOM — the human-readable sub-inventory designation (the operational "stock room"), the attribute most often surfaced in reports.
• STOCK_ROOM_DP — the descriptive/friendly display form of the stock room, used for presentation-layer labeling.
• PLANT_FK_KEY — the organization-level foreign key that positions this sub-inventory within its parent inventory organization (the "plant" grain), enabling roll-up from sub-inventory to organization.
• NAME and DESCRIPTION — the dimension's name and descriptive text, used for dimension labeling and search.
• INSTANCE_CODE — identifies the source instance or environment from which the dimension row was extracted, supporting multi-instance consolidation.
• CREATION_DATE and LAST_UPDATE_DATE — standard audit timestamps used for incremental extract and change detection.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — the standard five descriptive flexfield-style columns reserved for customer-specific dimension attributes.

Common Use Cases and Queries

Because this is a dimension/level table, the dominant access pattern is joining it to fact-style inventory or ILDM tables on the surrogate key, or resolving a user-supplied stock room value to its surrogate. A typical lookup by business key is:

• SELECT stock_room_pk, stock_room, plant_fk_key FROM opi.edw_mtl_ildm_sub_inv_ltc WHERE stock_room = :p_stock_room;
• SELECT stock_room_pk, stock_room_pk_key, stock_room_dp FROM opi.edw_mtl_ildm_sub_inv_ltc WHERE plant_fk_key = :p_plant; — list all sub-inventories for an organization.
• Incremental extraction for downstream ETL: SELECT * FROM opi.edw_mtl_ildm_sub_inv_ltc WHERE last_update_date >= :p_since;

Reporting use cases include sub-inventory-level inventory balance roll-ups, stockroom utilization dashboards, and dimension enrichment where an OPI extract fact is decorated with STOCK_ROOM_DP for display. In EBS-proper reporting, the same sub-inventory concept is sourced from MTL_SECONDARY_INVENTORIES; this OPI object serves as its dimensional representation.

Related Objects

• MTL_SECONDARY_INVENTORIES — the operational EBS sub-inventory master; the source of the business values mapped into STOCK_ROOM.
• MTL_PARAMETERS and ORG_ORGANIZATION_DEFINITIONS — resolve the PLANT_FK_KEY back to the inventory organization.
• EDW_MTL_ILDM_ORG_LTC — the parent organization level table, joined via PLANT_FK_KEY for hierarchy roll-ups.
• EDW_MTL_ILDM_LOCATOR_LTC — the finer locator-level dimension that further refines sub-inventory positioning.
• EDW_MTL_ILDM_INV_LTC — the inventory/item level table combined with this one for sub-inventory-by-item analyses.
• Other ILDM fact and extract tables keyed on STOCK_ROOM_PK, which reference this level via the EDW_MTL_ILDM_SUB_INV_LTC_U surrogate key.

Given the standalone classification and obsolete status, integrators should confirm the presence and population of this table in their specific OPI/ETRM installation before relying on it in new interfaces.