Search Results ego_value_set_ext




Overview
Explore in AI Chat

Overview

EGO_VALUE_SET_EXT is a table in the EGO schema (Advanced Product Catalog) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to capture parent-child relationships between value sets. Value sets in EGO provide the controlled vocabularies used to constrain attribute values in the Advanced Product Catalog; EGO_VALUE_SET_EXT records which value sets are hierarchically subordinate to which other value sets, enabling cascading or dependent value selection. The table is part of the Oracle Proprietary, Confidential Information classification and holds a status of VALID in the ETRM 12.2.2 documentation set. From a heuristic Data Vault modeling perspective (mined from the observed foreign key structure), the table is classified as standalone. This suggests that EGO_VALUE_SET_EXT does not act as a pure link between two independently tracked hubs in the manner of a classic link table; instead it functions as a self-referencing extension that carries its own descriptive and audit content, and thus is best modeled as a satellite or extension of the value-set business entity rather than a central hub.

Key Information Stored

The documented physical schema contains seven columns. The two most important business columns are:

  • VALUE_SET_ID — the identifier of the child (dependent) value set. This column is the primary component of both unique indexes and is the foreign key target that links the row back to the parent value-set definition.
  • PARENT_VALUE_SET_ID — the identifier of the value set that serves as the parent in the hierarchy. This is the self-referencing column that expresses the parent-child relationship.
  • CREATED_BY, CREATION_DATE — the standard EBS audit columns recording who created the relationship row and when.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS audit columns recording the most recent modification to the row and the login session that performed it.

Two unique indexes are documented as business-key candidates: EGO_VALUE_SET_EXT_U1 on (VALUE_SET_ID, PARENT_VALUE_SET_ID), and EGO_VALUE_SET_EXT_U2 on (VALUE_SET_ID) alone. Notably, the absence of a separately documented surrogate primary key column, combined with the single-column unique constraint on VALUE_SET_ID, indicates that each child value set may be associated with at most one parent — the relationship is effectively one-to-many from parent to children. This is a key modeling observation for anyone reading or joining the table.

Common Use Cases and Queries

Typical uses include validating value-set hierarchies, reporting dependent value sets for catalog configuration, and migrating or auditing parent-child structures. A sample query that resolves parents from their children:

  • SELECT child.VALUE_SET_ID, child.PARENT_VALUE_SET_ID, parent.VALUE_SET_NAME FROM EGO_VALUE_SET_EXT child, FRM_PART_VALUE_SETS parent WHERE child.PARENT_VALUE_SET_ID = parent.VALUE_SET_ID;
  • Detecting orphan or root records: SELECT * FROM EGO_VALUE_SET_EXT WHERE PARENT_VALUE_SET_ID IS NULL OR PARENT_VALUE_SET_ID NOT IN (SELECT VALUE_SET_ID FROM FRM_PART_VALUE_SETS);
  • Audit reporting using CREATION_DATE and LAST_UPDATE_DATE to identify recently reconfigured hierarchies.

Because EGO extends beyond the core inventory model, join logic should always enforce the FRM_PART_VALUE_SETS relationship to avoid returning value-set identifiers that have no corresponding base definition.

Related Objects

The following objects are most significant for joining or interpreting this table:

  • FRM_PART_VALUE_SETS — referenced via EGO_VALUE_SET_EXT.VALUE_SET_ID; the primary base definition of value sets and the target of the documented foreign key.
  • EGO_VALUE_SET_EXT_U1, EGO_VALUE_SET_EXT_U2 — the unique indexes (business-key candidates) that enforce hierarchy uniqueness on (VALUE_SET_ID, PARENT_VALUE_SET_ID) and single-parent integrity on VALUE_SET_ID.
  • EGO_VALUE_SET_EXT self-referencing relationship — via VALUE_SET_ID to PARENT_VALUE_SET_ID, used to traverse multi-level hierarchies.
  • The EGO Advanced Product Catalog attribute and value-set assignment objects that consume the hierarchies defined here for dependent value selection.

Because the documented metadata identifies EGO_VALUE_SET_EXT as standalone with only a single foreign key to FRM_PART_VALUE_SETS, integrators should treat this table as an extension layer over the value-set master data rather than a central transaction table, and should rely on the unique indexes to determine expected cardinality when building downstream joins.