Search Results edw_local_flex_seg_mappings_v

Overview

EDW_LOCAL_FLEX_SEG_MAPPINGS_V is a dictionary view in the Oracle E-Business Suite (EBS) environment, associated with the BIS – Applications BIS product family. The BIS (Business Intelligence System) module hosts the Oracle EBS data warehouse and analytical reporting infrastructure. Views prefixed with EDW_ ("Enterprise Data Warehouse") are used by the Oracle Business Intelligence and Daily Business Intelligence (DBI) layers to expose mapping metadata from the transactional EBS database into the reporting and analytics environment.

This view presents segment-mapping metadata that links Oracle General Ledger (GL) key flexfield structures, their segments, and their underlying value sets to the logical dimensions used in the EDW star schemas. In practical terms, it tells the reporting layer which key flexfield segment maps to which EDW dimension, and identifies the column name and value set associated with that segment. The STRUCTURE_NUM column, which users most commonly search on, is the numeric identifier of a key flexfield structure (the "chart of accounts" structure in the GL context) and is the primary join key to the underlying key flexfield definition tables.

Underlying Base Objects

The ETRM metadata for release 12.2.2 documents this view as defined over a single base object: EDW_LOCAL_FLEX_SEG_MAPPINGS. The view performs a straightforward projection (no joins, filters, or transformations are visible in the view text), selecting all documented columns from the base table. Because the metadata notes "Referenced base objects: none documented" for the underlying table, the lineage is intentionally shallow — the view functions as a stable public interface over a warehouse-managed table whose physical definition is created and maintained by the BIS/EDW installation scripts and concurrent programs rather than by the core applications schema.

Implementation notes in the reference documentation indicate the view is a repository-defined object and may be absent or unimplemented in databases where the EDW/BIS repository has not been installed or fully populated. Administrators should therefore verify existence in ALL_VIEWS/DBA_VIEWS before relying upon it.

Key Columns

• DIMENSION_SHORT_NAME — The short name of the EDW logical dimension to which the segment is assigned.
• STRUCTURE_NUM — Numeric identifier of the key flexfield structure; the primary key component for identifying a specific chart of accounts or other flexfield structure.
• STRUCTURE_NAME — Descriptive name of the flexfield structure corresponding to STRUCTURE_NUM.
• APPLICATION_COLUMN_NAME — The database column name that stores the segment value in the underlying application table.
• SEGMENT_NAME — The name of the individual segment (for example, "Company", "Cost Center", "Account").
• ID_FLEX_CODE — Identifier of the key flexfield (for example, GL# for the Accounting Flexfield).
• APPLICATION_ID — The application owning the flexfield definition.
• VALUE_SET_NAME / VALUE_SET_ID — The value set that validates and stores values for the segment.
• INSTANCE_CODE — Code identifying the EDW instance or source context for the mapping.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE record creation and maintenance history.

Common Use Cases and Queries

Typical uses include validating EDW dimension mappings before report deployment, troubleshooting mismatched chart-of-accounts structures in a DBI extract, and documenting the flexfield-to-dimension lineage for data governance. Because STRUCTURE_NUM is the most frequently searched term, the following query retrieves all segment mappings for one structure:

SELECT dimension_short_name,
       structure_num,
       structure_name,
       segment_name,
       application_column_name,
       value_set_name
FROM   edw_local_flex_seg_mappings_v
WHERE  structure_num = :structure_num
ORDER  BY segment_name;

To inventory all dimensions and their associated segments across the instance, use:

SELECT DISTINCT dimension_short_name, id_flex_code, structure_name
FROM   edw_local_flex_seg_mappings_v
ORDER  BY dimension_short_name;

For reconciliation, analysts may join the view on ID_FLEX_CODE and STRUCTURE_NUM to FND_ID_FLEX_STRUCTURES to compare EDW mappings against the live applications flexfield definitions.