Search Results ego_value_set_ext_u2




Overview
Explore in AI Chat

Overview

EGO.EGO_VALUE_SET_EXT is a transactional table in the Oracle E-Business Suite EGO schema that captures parent-child relationships between value sets. It is the extension structure used by the EGO (Advanced Product Catalog / Item Management) product family to record hierarchy and lineage between flex value sets, enabling dependent and independent value set definitions to be modeled explicitly rather than inferred. The table resides in the APPS_TS_TX_DATA tablespace and is registered under FND Design Data as EGO.EGO_VALUE_SET_EXT. Its status is VALID in both 12.1.1 and 12.2.2.

From a dimensional modeling perspective, the documented FK and unique-index structure suggests this object behaves as a link — a relationship table joining two instances of the same domain (a child value set and its parent). The heuristic Data Vault classification is therefore best treated as a modeling suggestion rather than a firm designation; the two-column business key (VALUE_SET_ID plus PARENT_VALUE_SET_ID) is what gives the table its purpose.

Key Information Stored

The table holds seven documented columns. The two business columns form the operational core:

Two unique indexes are documented, both in APPS_TS_TX_IDX. EGO_VALUE_SET_EXT_U1 is a composite unique index on (VALUE_SET_ID, PARENT_VALUE_SET_ID) and represents the full business-key candidate for the relationship. EGO_VALUE_SET_EXT_U2 is a unique index on VALUE_SET_ID alone, enforcing a one-parent-per-child cardinality: a child value set may appear only once, so each value set can have at most one recorded parent.

Common Use Cases and Queries

Typical usage is hierarchy traversal and dependency reporting. The U2 index makes single-parent lookups by child extremely efficient:

  • Retrieve the parent of a given value set: SELECT PARENT_VALUE_SET_ID FROM EGO.EGO_VALUE_SET_EXT WHERE VALUE_SET_ID = :child_id;
  • List all children of a parent: SELECT VALUE_SET_ID FROM EGO.EGO_VALUE_SET_EXT WHERE PARENT_VALUE_SET_ID = :parent_id;
  • Full extraction for ETL or reconciliation: SELECT VALUE_SET_ID, PARENT_VALUE_SET_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN FROM EGO.EGO_VALUE_SET_EXT;
  • Join both sides back to the base value set definition to resolve names: join VALUE_SET_ID to FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID for the child and again via an alias for the parent.

Reporting scenarios include identifying value sets that participate in parent-child structures, auditing the completeness of hierarchy configuration, and diagnosing value set validation behavior where dependent value sets are defined.

Related Objects

The most significant related objects are the value set base tables and the constraint that depends on this table:

  • FND_FLEX_VALUE_SETS — joined on FLEX_VALUE_SET_ID = VALUE_SET_ID (child) and FLEX_VALUE_SET_ID = PARENT_VALUE_SET_ID (parent); the primary reference target for both business columns.
  • FRM_PART_VALUE_SETS — documented FK target for VALUE_SET_ID per the ETRM relationship data.
  • EGO_VALUE_SET_EXT# — the internal dependent object referenced by EGO.EGO_VALUE_SET_EXT, used by Oracle's constraint and index infrastructure.
  • FND_FLEX_VALUES / FND_FLEX_VALUES_TL — value-level children of the value sets referenced here, indirectly related through the child value set.
  • EGO_VALUE_SET_EXT_U1 / EGO_VALUE_SET_EXT_U2 — the unique indexes that enforce the documented business keys and drive query performance.

No documented object depends on this table beyond the referenced internal structure and its own indexes, confirming its role as a standalone relationship table.