Search Results edw_mtl_ildm_ou_lcv




Overview
Explore in AI Chat

Overview

The EDW_MTL_ILDM_OU_LCV view is an Oracle E-Business Suite database object owned by the APPS schema within the OPI (Operations Intelligence) product family. It is a source view that supplies the Operating Unit level of the Inventory Locator Dimension used by the Oracle EBS data warehouse and embedded analytics. The Inventory Locator Dimension allows reporting tools and Discoverer workbooks to analyze inventory balances by the organizational structures that own them. Specifically, this view runs at the Operating Unit grain, one level above the Inventory Organization grain, and it provides the descriptive attributes required to translate an operating unit primary key into meaningful report labels such as name, business group, organization type, and cost method context.

Because the view carries historical date range columns (DATE_FROM and DATE_TO) along with internal/external and instance flags, it supports both current and point-in-time dimensional reporting. This makes it suitable for joining to fact tables such as inventory transaction or on-hand fact views where denormalized operating unit attributes are required without a runtime lookup against transactional tables.

Underlying Base Objects

The view is defined over a single documented source object: EDW_ORGA_OPER_UNIT_LCV. The view text projects a consistent set of columns from that source, adding the literal value 'ALL' as the ALL_FK column, which is a common technique in Oracle Business Intelligence dimensional views. This constant is typically used to satisfy a foreign key for an "All" or total-level member of a hierarchy, enabling aggregate rollups without violating referential expectations in the ETL or BI tool layer.

Although the ETRM metadata lists no further documented base tables, the naming convention (LCV denotes a "List of Values Class View" style construct, and EDW denotes the Enterprise Data Warehouse layer) indicates that EDW_ORGA_OPER_UNIT_LCV is itself a derived view over the standard Oracle inventory organization and operating unit tables. The prefix ORGA aligns to the Organization dimension family. Practically, this means querying EDW_MTL_ILDM_OU_LCV is a read-only, denormalized operation with no write semantics and no triggers, so it is safe to use in ad hoc reporting and ETL extracts.

Key Columns

  • OPERATING_UNIT_PK – Surrogate primary key identifying the operating unit; used as the join key into fact tables.
  • OPERATING_UNIT_DP – Descriptive/display pointer for the operating unit, typically the human-readable value shown in reports.
  • NAME – Name of the operating unit.
  • BUSINESS_GROUP – Business group to which the operating unit belongs, enabling HR/ledger-style grouping.
  • ORG_TYPE – Organization type of the operating unit. This is the attribute most commonly searched for, since it distinguishes operating units from other organization classifications.
  • ORG_CODE – Short code for the organization, useful as a report label or filter.
  • PRIMARY_CST_MTHD – Primary costing method associated with the organization.
  • DATE_FROM / DATE_TO – Effective date range supporting historical and point-in-time queries.
  • INT_EXT_FLAG – Flag indicating internal versus external classification.
  • INSTANCE – Identifies the source instance, relevant in multi-instance warehouse loads.
  • ALL_FK – Constant 'ALL' member supporting total-level rollups.
  • USER_ATTRIBUTE1 through USER_ATTRIBUTE5 – Descriptive flexfield attributes for customer-specific extensions.
  • CREATION_DATE / LAST_UPDATE_DATE – Audit columns. Note the view text projects LAST_UPDATE_DATE twice, so callers should reference columns by name rather than positional index.

Common Use Cases and Queries

Reports and extracts that need operating unit display attributes for inventory analysis commonly query this view. A typical query lists operating units by organization type:

  • SELECT operating_unit_pk, name, org_type, org_code FROM edw_mtl_ildm_ou_lcv WHERE org_type = :org_type;
  • SELECT operating_unit_pk, name, business_group, primary_cst_mthd FROM edw_mtl_ildm_ou_lcv ORDER BY name;
  • Join to fact tables on OPERATING_UNIT_PK to enrich inventory facts, and join on ALL_FK when computing total-level aggregates.

Because the view is a pass-through over EDW_ORGA_OPER_UNIT_LCV, filter pushdown is preserved and performance depends on that underlying object. Restrict queries with ORG_TYPE, ORG_CODE, or the DATE_FROM/DATE_TO pair to limit result sets and support effective-dated reporting.