Search Results edw_local_flex_seg_mappings

Overview

The EDW_LOCAL_FLEX_SEG_MAPPINGS table is a BIS (Business Intelligence System) repository object within the Oracle EBS Applications BIS product family. It belongs to the Enterprise Data Warehouse (EDW) layer and serves as a mapping bridge between locally defined key flexfield segment structures and the applications that consume them for reporting and dimensional analysis. Rather than storing transactional accounting data, this table captures metadata that describes how a key flexfield dimension maps to an application column, a value set, and an instance context — enabling the EDW to reconcile segment-level definitions across general ledger structures.

The object resides in the BIS schema and is documented with 15 columns in ETRM 12.1.1. The heuristic Data Vault classification mined from its foreign key structure identifies this table as standalone. In Data Vault modeling terms, this suggests the object functions independently rather than as a natural hub, link, or satellite — it is best treated as a mapping or reference construct. Any modeling exercise should validate this suggestion against the actual ETL lineage before committing to a hub or satellite design. Notably, the ETRM documentation records this table as not implemented in this database at the time of extraction; DBAs should confirm its physical presence in the target instance.

Key Information Stored

The table's business content describes the relationship between flexfield dimensions and their technical mappings. The most significant columns are:

• DIMENSION_SHORT_NAME — the abbreviated identifier of the analytical dimension used in the EDW.
• STRUCTURE_NUM and STRUCTURE_NAME — the accounting or key flexfield structure to which the dimension belongs.
• APPLICATION_COLUMN_NAME — the physical column in the consuming application that carries the segment value.
• SEGMENT_NAME — the individual flexfield segment being mapped.
• ID_FLEX_CODE and APPLICATION_ID — the flexfield registration identifier and the owning application, forming a natural composite business key.
• VALUE_SET_NAME and VALUE_SET_ID — the value set definition governing permissible segment values; the latter is a documented foreign key pointing to FRM_PART_VALUE_SETS.
• INSTANCE_CODE — the flexfield instance context distinguishing multiple deployments of the same structure.
• Audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) — standard EBS who-columns supporting change tracking.

No surrogate primary key is enumerated in the documented column set; the effective uniqueness constraint is most likely carried by the combination of ID_FLEX_CODE, APPLICATION_ID, STRUCTURE_NUM, SEGMENT_NAME, and INSTANCE_CODE.

Common Use Cases and Queries

This table is primarily consumed by EDW load processes and BI metadata reconciliation reports. Typical scenarios include verifying that every flexfield segment used in a GL structure has a corresponding EDW dimension mapping, and resolving a value set identifier back to its definition for validation.

A representative query joins to the value set reference:

• SELECT m.dimension_short_name, m.segment_name, m.value_set_name, v.* FROM edw_local_flex_seg_mappings m, frm_part_value_sets v WHERE m.value_set_id = v.value_set_id;
• Filtering by id_flex_code and application_id isolates the mappings for a specific flexfield structure.
• Grouping by structure_name produces a segment inventory report for EDW governance.

Because the object is reported as unimplemented in the source ETRM database, queries should first confirm existence via ALL_TABLES before being promoted to production ETL jobs.

Related Objects

The documented foreign key relationship identifies the primary upstream dependency:

• FRM_PART_VALUE_SETS — referenced through VALUE_SET_ID; supplies value set definitions for each mapped segment.
• FND_ID_FLEX_STRUCTURES — the canonical definition of key flexfield structures keyed by ID_FLEX_CODE and APPLICATION_ID.
• FND_SEGMENT_ATTRIBUTES — supplies segment-level attributes referenced by SEGMENT_NAME.
• FND_FLEX_VALUE_SETS — the standard flexfield value set registry underlying VALUE_SET_NAME.
• EDW dimension and staging tables in the BIS schema — consume these mappings during warehouse load.

DBAs should treat this object as EDW metadata rather than a transactional source, and validate its population through the BIS ETL layer.