Search Results lucd_start_date_active

Overview

POA.EDW_LOOKUP_M is a denormalized star-schema dimension table within the Oracle E-Business Suite data warehouse layer, owned by the POA schema and registered under FND Design Data as BIS.EDW_LOOKUP_M. It serves as the Lookup dimension, storing records for the lowest level of the Lookup dimension together with their full parentage up to the top level, across all hierarchies. In Oracle EBS 12.1.1 and 12.2.2, the table is stored in the APPS_TS_SUMMARY tablespace, which is characteristic of summary-level reporting structures populated through ETL rather than transactional online activity. The table is flagged as VALID and carries a PCT Free setting of 30, consistent with a read-mostly analytical object subject to periodic full or incremental refreshes.

From a Data Vault modeling perspective, the object is heuristically classified as standalone, as no foreign-key relationships were mined from the documented structure. In practice, however, EDW_LOOKUP_M behaves functionally as a satellite-like denormalized dimension whose natural business key is the combination of lookup code attributes captured in the unique indexes, rather than as a classic hub or link. This classification should be treated as a modeling suggestion only; the table's actual role is a query-optimized dimension for lookup value reporting.

Key Information Stored

The table contains 21 documented columns. The most significant are described below.

• LUCD_LOOKUP_CODE_PK_KEY (NUMBER) — System-generated unique identifier and the primary key of EDW_LOOKUP_M_PK. It is also the sole column of the unique index EDW_LOOKUP_M_U2, making it the cleanest single-column surrogate key for joins and reporting.
• LUCD_LOOKUP_CODE_PK (VARCHAR2 240) — Unique identifier at the business level; it participates with LUCD_LOOKUP_CODE_PK_KEY in the unique index EDW_LOOKUP_M_U1, forming a documented business-key candidate.
• LUCD_LOOKUP_CODE_DP (VARCHAR2 240) — Unique Lookup Code name, typically the display form of the lookup value.
• LUCD_LOOKUP_CODE (VARCHAR2 80) and LUCD_LOOKUP_TYPE (VARCHAR2 80) — The lookup code and its type, the core classifying attributes for any lookup record.
• LUCD_NAME (VARCHAR2 240) and LUCD_DESCRIPTION (VARCHAR2 240) — Human-readable name and description of the lookup value.
• LUCD_START_DATE_ACTIVE and LUCD_END_DATE_ACTIVE (DATE) — Effective dating for the lookup value, supporting as-of reporting.
• LUCD_TABLE_CODE (VARCHAR2 20) and LUCD_INSTANCE (VARCHAR2 40) — Identify which source application table or entry the lookup originates from, and the instance identifier.
• ALL_ALL_PK, ALL_ALL_PK_KEY, and ALL_NAME — The top level of the Lookup dimension, with ALL_NAME (VARCHAR2 80) giving the top-level label and the PK/KEY columns providing its identifiers.
• LUCD_USER_ATTRIBUTE1 through LUCD_USER_ATTRIBUTE5 (VARCHAR2 240 each) — User-defined attribute extension columns.
• CREATION_DATE and LAST_UPDATE_DATE (DATE) — Standard Who columns for load auditing.

Common Use Cases and Queries

Because EDW_LOOKUP_M is a summary dimension, it is primarily used to resolve lookup codes to their descriptive names and to traverse hierarchy levels from the lowest lookup value to the top-level ALL_NAME grouping. Typical reporting scenarios include lookup value inventories, effective-dating analysis, and source-table provenance reporting.

A representative query retrieving lookup values with their top-level parentage:

• SELECT l.LUCD_LOOKUP_TYPE, l.LUCD_LOOKUP_CODE, l.LUCD_NAME, l.LUCD_DESCRIPTION, l.ALL_NAME FROM POA.EDW_LOOKUP_M l WHERE l.LUCD_LOOKUP_TYPE = :type;

A point lookup by the surrogate key uses the U2 index:

• SELECT * FROM POA.EDW_LOOKUP_M WHERE LUCD_LOOKUP_CODE_PK_KEY = :id;

Effective-dating analysis applies the start and end date columns:

• SELECT LUCD_LOOKUP_CODE, LUCD_START_DATE_ACTIVE, LUCD_END_DATE_ACTIVE FROM POA.EDW_LOOKUP_M WHERE TRUNC(SYSDATE) BETWEEN LUCD_START_DATE_ACTIVE AND NVL(LUCD_END_DATE_ACTIVE, TRUNC(SYSDATE));

These patterns leverage EDW_LOOKUP_M_U1 and EDW_LOOKUP_M_U2, both hosted in APPS_TS_SUMMARY, and are suitable for concurrent-program extracts and BI publisher reports.

Related Objects

The documented metadata records no foreign-key relationships, consistent with the standalone Data Vault classification. The following related objects are the most significant based on the Lookup dimension context.

• POA.EDW_LOOKUP_M — The subject table itself; the primary key EDW_LOOKUP_M_PK on LUCD_LOOKUP_CODE_PK_KEY and the unique indexes U1 and U2 are the joining surfaces for any dependent object.
• FND_LOOKUP_VALUES — The Oracle EBS base table from which lookup code values, types, meanings, descriptions, and effective dates are commonly sourced during ETL into EDW_LOOKUP_M.
• FND_LOOKUP_TYPES — The parent reference for lookup types (LUCD_LOOKUP_TYPE) and their descriptions.
• POA.EDW_LOOKUP_M_U1 and POA.EDW_LOOKUP_M_U2 — The two unique indexes in APPS_TS_SUMMARY that back the business-key and surrogate-key access paths.
• BIS.EDW_LOOKUP_M — The FND Design Data registration, which governs the object's definition and its propagation across EBS 12.1.1 and 12.2.2 environments.
• Other POA.EDW_* summary tables — Sibling dimension and fact tables in the POA schema that join to EDW_LOOKUP_M on LUCD_LOOKUP_CODE_PK_KEY for lookup enrichment.

Any dependent object should join through LUCD_LOOKUP_CODE_PK_KEY, the single-column unique key, as the most reliable and index-supported access path.