Results for “db_object_reference_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

WMS_DB_OBJ_REF_MEMBERS is a table in the WMS (Warehouse Management) schema of Oracle E-Business Suite, described as "WMS Database Object Reference Members." It functions as a child table within the WMS metadata/reference model, capturing the individual members that compose a database object reference definition. Where WMS_DB_OBJECT_REFERENCES establishes the reference header, WMS_DB_OBJ_REF_MEMBERS stores the ordered list of entries belonging to that reference, one row per member, sequenced by SEQUENCE_NUMBER. The object is documented as VALID in ETRM for 12.1.1 and 12.2.2, with a documented physical schema of 27 columns owned by WMS.

From a modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is link. This suggests the table behaves as an associative entity connecting WMS_DB_OBJECT_REFERENCES to WMS_DB_OBJECTS, rather than as a standalone hub of business entities or a descriptive satellite.

Key Information Stored

The table is anchored by composite primary key WMS_DB_OBJ_REF_MEMBERS_PK on (DB_OBJECT_REFERENCE_ID, SEQUENCE_NUMBER), which uniquely identifies each member row within its parent reference. The columns most relevant to interpretation and use are:

• DB_OBJECT_REFERENCE_ID — foreign key to WMS_DB_OBJECT_REFERENCES; identifies the owning reference header.
• SEQUENCE_NUMBER — orders the members within the reference and participates in the primary key.
• DB_OBJECT_ID — foreign key to WMS_DB_OBJECTS; identifies the specific database object that is a member of the reference.
• TABLE_ALIAS — alias assigned to the member object, typically used when the same underlying object is referenced more than once under different aliases.
• USER_DEFINED_FLAG — indicates whether the member entry is a seeded/system definition or a user-defined addition.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield columns for extensible, installation-specific member attributes.
• ZD_EDITION_NAME — the edition column supporting Oracle's Edition-Based Redefinition (EBR), used in 12.2.x online patching.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard who-column audit trail.

Beyond the primary key, a unique index WMS_DB_OBJ_REF_MEMBERS_U1 exists on (DB_OBJECT_REFERENCE_ID, SEQUENCE_NUMBER, ZD_EDITION_NAME), which confirms the member sequence as the business-key candidate within an edition.

Common Use Cases and Queries

This table is primarily queried for metadata inspection — determining which database objects participate in a given WMS reference definition, and in what order. A typical listing query joins the member table to WMS_DB_OBJECTS to resolve object names:

• Enumerating members of a reference: select M.SEQUENCE_NUMBER, O.OBJECT_NAME, M.TABLE_ALIAS from WMS_DB_OBJ_REF_MEMBERS M join WMS_DB_OBJECTS O on O.DB_OBJECT_ID = M.DB_OBJECT_ID where M.DB_OBJECT_REFERENCE_ID = :ref_id order by M.SEQUENCE_NUMBER.
• Auditing user-defined extensions: filtering on USER_DEFINED_FLAG = 'Y' to isolate members added outside seeded definitions.
• Flexfield-based reporting: selecting from the ATTRIBUTE_CATEGORY / ATTRIBUTE1–15 columns once a descriptive flexfield has been configured on the member entity.
• Edition-aware validation in 12.2.x: including ZD_EDITION_NAME in predicates and joins to scope results to the active edition.

Related Objects

• WMS_DB_OBJECT_REFERENCES — parent header table; joined via WMS_DB_OBJ_REF_MEMBERS.DB_OBJECT_REFERENCE_ID.
• WMS_DB_OBJECTS — the object registry; joined via WMS_DB_OBJ_REF_MEMBERS.DB_OBJECT_ID to resolve the member object itself.
• WMS_DB_OBJ_REF_MEMBERS_PK — the composite primary key constraint enforcing member uniqueness.
• WMS_DB_OBJ_REF_MEMBERS_U1 — the unique index on (DB_OBJECT_REFERENCE_ID, SEQUENCE_NUMBER, ZD_EDITION_NAME) supporting business-key and edition lookups.

Together these objects form the WMS database-object reference subsystem, with WMS_DB_OBJ_REF_MEMBERS acting as the associative link between reference definitions and the physical objects they enumerate.