Results for “edw_mtl_ildm_plant_ltc”

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

Overview

The EDW_MTL_ILDM_PLANT_LTC table is an Oracle Business Intelligence System (BIS) warehouse object owned by the OPI schema. In Oracle EBS 12.1.1 and 12.2.2, it serves as an enterprise data warehouse (EDW) staging and dimension structure that consolidates plant-level (organization-level) inventory data for analytical reporting. The "MTL_ILDM" segment indicates a link to the Oracle Inventory (MTL) module and the Inventory Logistics Data Model, while the "PLANT_LTC" segment denotes the plant-level local table copy consumed by BI reporting and OBIEE/EDW extracts. Its role is to hold denormalized plant or warehouse organization records that feed inventory, costing, and logistics dashboards without querying transactional tables directly.

The heuristic Data Vault classification for this object is standalone, meaning the mined foreign-key structure does not reveal a strong dependency on parent hubs or links. In practice, the table behaves most like a satellite or type-1 dimension holding descriptive plant attributes around a single business key, rather than a true Data Vault hub with generated hash keys. It should therefore be treated as a reporting dimension sourced from EBS master data.

Key Information Stored

The table exposes 17 documented columns. The most important fields are:

• PLANT_PK — the surrogate primary key, enforced by the unique index EDW_MTL_ILDM_PLANT_LTC_U. This is the technical row identifier.
• PLANT_PK_KEY — the business-key candidate, enforced by the unique index EDW_MTL_ILDM_PLANT_LTC_UKEY. This is the natural key used for source-system alignment.
• ORGANIZATION_CODE — the inventory organization short code (e.g., the three-character plant identifier).
• ORGANIZATION_NAME — the descriptive name of the plant or warehouse organization.
• NAME and DESCRIPTION — the display label and free-text description of the plant record.
• OPERATING_UNIT_FK_KEY — foreign-key reference to the operating unit that owns the organization.
• OPM_ORGANIZATION_FK_KEY — foreign-key reference to the Process Manufacturing (OPM) organization, linking the plant to OPM inventory structures.
• INSTANCE_CODE — the EBS instance or source-system identifier, used to separate data from multiple instances loaded into the EDW.
• PLANT_DP — the plant data-provider or processing flag associated with the plant record.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — five extensibility columns reserved for customer-defined plant-level attributes.
• CREATION_DATE and LAST_UPDATE_DATE — standard EBS audit columns recording row creation and last modification timestamps.

The surrogate key PLANT_PK should be treated as the join key for internal warehouse relationships, while PLANT_PK_KEY is the candidate for reconciliation against source EBS organization identifiers.

Common Use Cases and Queries

This table is typically queried as a plant dimension for inventory and costing analytics. A common pattern joins the plant dimension to inventory fact tables on the surrogate key:

• Plant-level inventory valuation and on-hand reporting by organization code.
• Operating-unit roll-ups that aggregate plant metrics to the legal entity level using OPERATING_UNIT_FK_KEY.
• OPM-specific reporting that filters or groups by OPM_ORGANIZATION_FK_KEY.
• Multi-instance consolidation, filtering by INSTANCE_CODE to isolate a single EBS source.
• Customer-specific reporting driven by the USER_ATTRIBUTE1..5 columns.

A representative query retrieves active plant records with their operating unit context:

SELECT p.PLANT_PK, p.ORGANIZATION_CODE, p.ORGANIZATION_NAME,
  p.OPERATING_UNIT_FK_KEY, p.INSTANCE_CODE
  FROM OPI.EDW_MTL_ILDM_PLANT_LTC p
 WHERE p.INSTANCE_CODE = :instance
  ORDER BY p.ORGANIZATION_CODE;

Because the table carries audit columns, incremental extracts should be driven from LAST_UPDATE_DATE to capture only changed plant records.

Related Objects

As a standalone warehouse dimension, EDW_MTL_ILDM_PLANT_LTC is referenced primarily through its SQL join keys rather than enforced foreign keys. The most significant related objects are:

• EDW_MTL_ILDM_ITEM_LTC — the item-level counterpart table in the same ILDM family, joined by plant key or organization code.
• EDW_MTL_ILDM_INV_LTC — inventory fact or level table joined to plants via PLANT_PK or PLANT_PK_KEY.
• HR_ALL_OPERATING_UNITS — the EBS source for operating unit descriptions matched on OPERATING_UNIT_FK_KEY.
• ORG_ORGANIZATION_DEFINITIONS — the source inventory organization view that supplies ORGANIZATION_CODE, ORGANIZATION_NAME, and class information.
• OPM_ORGANIZATION_HEADERS — the Process Manufacturing organization header referenced by OPM_ORGANIZATION_FK_KEY.
• GL_LEDGERS / GL_LEGAL_ENTITIES — used for legal-entity roll-up reporting tied to the operating unit.
• MTL_PARAMETERS — the source of inventory organization parameters that often enrich plant dimensions.
• EDW_MTL_ILDM_ORG_LTC — related organization-level staging table in the same EDW load family.

These objects should be validated against the deployed 12.1.1 or 12.2.2 instance, since column availability in the OPI schema may vary by EDW patch level.