Search Results edw_lookup_m




Overview
Explore in AI Chat

Overview

EDW_LOOKUP_M is a BIS (Business Intelligence System / Applications BIS) reference table documented in the ETRM metadata for Oracle EBS 12.1.1 and 12.2.2. It belongs to the lookup and reference-data family of objects that supply validated code values, descriptive names, and date-ranged activity flags to reporting and analytics layers. The physical schema documented for the 12.1.1 release places the object in the POA schema, with 21 columns. The metadata explicitly records the object as "Not implemented in this database," meaning it is a documented/legacy structure rather than an active, populated table in the reference environment. In heuristic Data Vault terms, the mined FK structure classifies EDW_LOOKUP_M as a standalone object. As a modeling suggestion, this classification implies the table behaves as a self-contained reference or hub-style set of lookup codes rather than a link or satellite dependent on another parent.

Key Information Stored

The table is keyed by EDW_LOOKUP_M_PK on the column LUCD_LOOKUP_CODE_PK_KEY. Two unique indexes are documented as business-key candidates: EDW_LOOKUP_M_U1, defined across LUCD_LOOKUP_CODE_PK and LUCD_LOOKUP_CODE_PK_KEY, and EDW_LOOKUP_M_U2, on LUCD_LOOKUP_CODE_PK_KEY alone. This distinction separates the surrogate primary key used for physical joins from the business-key candidates intended to enforce uniqueness at the operational level. The most important descriptive columns include LUCD_LOOKUP_CODE, which carries the code value itself; LUCD_NAME, the human-readable label; LUCD_DESCRIPTION, extended narrative text; and LUCD_LOOKUP_TYPE, which partitions codes into logical lookup categories. Validity windows are captured in LUCD_START_DATE_ACTIVE and LUCD_END_DATE_ACTIVE, the standard Oracle EBS effective-date pattern. LUCD_TABLE_CODE and LUCD_INSTANCE provide context about the source table and instance association. Five generic extensibility columns, LUCD_USER_ATTRIBUTE1 through LUCD_USER_ATTRIBUTE5, hold client-specific or implementation-specific values without schema modification. The remaining documented columns include LUCD_LOOKUP_CODE_PK, LUCD_LOOKUP_CODE_DP, ALL_ALL_PK, ALL_ALL_PK_KEY, ALL_NAME, CREATION_DATE, and LAST_UPDATE_DATE. The ALL_ALL_ columns and ALL_NAME suggest a denormalized association with a broader organization or entity dimension, a common pattern in BIS reporting schemas.

Common Use Cases and Queries

Because the object is a lookup repository, typical use cases center on decoding, filtering, and reporting against validated code sets. Analysts retrieve active codes for a given type, join codes to transactional fact tables, and resolve descriptions for presentation. A representative query pattern is:

  • SELECT LUCD_LOOKUP_CODE, LUCD_NAME, LUCD_DESCRIPTION FROM EDW_LOOKUP_M WHERE LUCD_LOOKUP_TYPE = :p_type AND SYSDATE BETWEEN LUCD_START_DATE_ACTIVE AND NVL(LUCD_END_DATE_ACTIVE, SYSDATE);
  • Joining to a fact or staging table on LUCD_LOOKUP_CODE to translate stored codes into readable names for BI reports.
  • Grouping by LUCD_TABLE_CODE or LUCD_INSTANCE to audit which source objects contribute to each lookup domain.
  • Using LUCD_USER_ATTRIBUTE1 through LUCD_USER_ATTRIBUTE5 as reporting filters when standard columns do not carry the required segmentation.

Effective-dating logic is essential in every case, since historical codes may remain valid with closed end dates.

Related Objects

The metadata classifies EDW_LOOKUP_M as standalone with no documented foreign keys originating from it. Reference tables of this kind are typically referenced by downstream fact, staging, and ETL mapping objects that join on LUCD_LOOKUP_CODE or LUCD_LOOKUP_CODE_PK_KEY. In the BIS/EDW reporting layer, the most significant related objects are the EDW-family fact and dimension tables whose codes are decoded through this lookup, along with standard EBS lookup views that expose equivalent functionality. Because the object is documented as not implemented, integrations should be validated against the active lookup sources in the target environment before relying on EDW_LOOKUP_M for production reporting.