Search Results eng_subject_entities




Overview
Explore in AI Chat

Overview

The ENG_SUBJECT_ENTITIES table resides in the ENG schema (Engineering product family) and is documented in the ETRM 12.2.2 reference as a table that "stores subject entities." It sits within the Oracle EBS Engineering module, which underpins product data management, bill-of-material structures, and engineering change infrastructure. In practical terms, the table captures hierarchical subject definitions — a "subject" being a named business object or entity type against which engineering rules, attributes, or relationship mappings are resolved. The presence of parent/child style columns (PARENT_ENTITY_NAME, SUBJECT_LEVEL) confirms that the table models multi-level entity hierarchies rather than a flat list.

The heuristic Data Vault classification mined from the foreign key structure is standalone. This is a modeling suggestion only, indicating that the table does not participate in a classic hub-and-link topology relative to its own primary key; its single outbound FK (SUBJECT_ID → IGS_UC_COM_EBL_SUBJ) is a parent reference rather than a sibling entity link. As a suggestion, a Data Vault modeler might treat the subject-entity rows as a reference/descriptive satellite attached to a subject hub originating in IGS_UC_COM_EBL_SUBJ, with ZD_EDITION_NAME supplying the edition/version dimension that makes the rows time-aware.

Key Information Stored

The table is documented with 16 physical columns. The most significant are:

  • SUBJECT_ID — the foreign key to IGS_UC_COM_EBL_SUBJ; identifies the parent subject to which the entity belongs. It is the leading column of both unique indexes, making it the primary grouping attribute.
  • ENTITY_NAME — the name of the child entity held under the subject; combined with SUBJECT_ID and ZD_EDITION_NAME it forms the business-key candidate ENG_SUBJECT_ENTITIES_U2.
  • PARENT_ENTITY_NAME — the ancestor entity, supporting recursive traversal of the hierarchy.
  • SUBJECT_LEVEL — the depth or tier of the entity within the subject hierarchy.
  • FK1_COLUMN_NAME through FK5_COLUMN_NAME — five generic slots that store the column names of the foreign keys that relate the subject entity to its underlying Engineering objects. This is a metadata-driven pattern, allowing one row to describe up to five key relationships without schema change.
  • BEAN_TYPE — identifies the Java/bean class or object type that materializes the subject entity within the Engineering framework.
  • ZD_EDITION_NAME — the edition/version discriminator; it appears in both unique indexes and is a defining attribute of Oracle's editioning model.
  • Standard audit columns CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN track row lifecycle.

No single-column surrogate primary key is documented. Instead, uniqueness is enforced by the composite business-key candidates ENG_SUBJECT_ENTITIES_U1 (SUBJECT_ID, SUBJECT_LEVEL, ZD_EDITION_NAME) and ENG_SUBJECT_ENTITIES_U2 (SUBJECT_ID, ENTITY_NAME, ZD_EDITION_NAME).

Common Use Cases and Queries

Typical usage centers on resolving the set of entities defined for a subject and walking their hierarchy. A representative query returning the top-level entities for one subject and edition is:

  • SELECT entity_name, subject_level, parent_entity_name FROM eng.eng_subject_entities WHERE subject_id = :subject_id AND zd_edition_name = :edition AND subject_level = 1;
  • Hierarchy reconstruction: order by subject_level and self-join on entity_name = parent_entity_name (within the same SUBJECT_ID/ZD_EDITION_NAME) to produce an indented tree.
  • Metadata inspection: query FK1_COLUMN_NAME…FK5_COLUMN_NAME to discover which underlying Engineering tables a given subject entity relates to, useful when writing dynamic or generic integration code driven by BEAN_TYPE.
  • Edition comparison: join on ZD_EDITION_NAME across editions to identify entities added or removed between versions.

Reporting generally filters by SUBJECT_ID first, since it is the leading column of both unique indexes and therefore the most selective access path.

Related Objects

The following objects are most significant in relation to ENG_SUBJECT_ENTITIES:

  • IGS_UC_COM_EBL_SUBJ — parent subject table; joined via ENG_SUBJECT_ENTITIES.SUBJECT_ID = IGS_UC_COM_EBL_SUBJ.SUBJECT_ID. This is the only documented foreign key relationship and the anchor of the subject hierarchy.
  • The Engineering entity/attribute base tables referenced dynamically through FK1_COLUMN_NAME–FK5_COLUMN_NAME, which resolve at runtime according to the subject definition.
  • FND_OBJECTS / FND_OBJECT_INSTANCES — metadata/definition tables commonly consulted to resolve BEAN_TYPE to an executable framework class.
  • Engineering change and BOM objects that consume subject-entity definitions when validating rule-driven edits.
  • Standard audit/editioning infrastructure: FND_AUDIT_COLUMNS and editioning views derived from ZD_EDITION_NAME.

Because the table is classified as standalone, joins beyond the IGS_UC_COM_EBL_SUBJ parent are typically driven by application logic embedded in FK*_COLUMN_NAME and BEAN_TYPE rather than declared referential constraints.