Results for “wms_db_object_reference_pk”

6 results




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

Overview

The WMS_DB_OBJECT_REFERENCES table is an Oracle Warehouse Management (WMS) schema object that stores metadata identifying database object references used throughout the WMS module. In Oracle EBS 12.1.1 and 12.2.2, this table acts as a central registry of database object identifiers that various WMS components — including parameters and object membership definitions — use to associate application logic with underlying database objects such as tables, views, or programmatic constructs.

From a data modeling perspective, the documented foreign-key structure (inbound references from WMS_DB_OBJ_REF_MEMBERS and WMS_PARAMETERS_B, with no outbound foreign keys to parent tables) suggests a hub-leaning classification. In Data Vault terms, this table behaves like a hub: it holds a single, stable business identifier (DB_OBJECT_REFERENCE_ID) that is referenced by multiple dependent tables, with descriptive attributes and audit columns contained in the same row rather than split into a separate satellite.

Key Information Stored

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

  • DB_OBJECT_REFERENCE_ID — The surrogate primary key enforced by the constraint WMS_DB_OBJECT_REFERENCE_PK. It is the identifier propagated to dependent tables.
  • DESCRIPTION — A human-readable description of the database object reference, used for identification and reporting.
  • USER_DEFINED_FLAG — Indicates whether the reference was seeded by Oracle or created by a user, which is important when distinguishing delivered versus custom configuration.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield (DFF) columns, enabling extensible, customer-defined attributes on each reference record.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard "Who" audit columns that support change tracking and compliance reporting.
  • ZD_EDITION_NAME — The Edition-Based Redefinition (EBR) column present in 12.2.x, enabling online patching and multi-edition support.

The unique index WMS_DB_OBJECT_REFERENCES_U1, defined on DB_OBJECT_REFERENCE_ID and ZD_EDITION_NAME, is the documented business-key candidate. The inclusion of ZD_EDITION_NAME reflects the 12.2.x EBR model, where the same logical identifier can exist across editions.

Common Use Cases and Queries

WMS administrators and developers query this table to resolve object reference identifiers back to descriptive records, to audit custom versus seeded entries, and to understand how parameters are bound to database objects.

A typical lookup joining to the parameters table:

  • SELECT p.PARAMETER_ID, p.DB_OBJECT_REFERENCE_ID, r.DESCRIPTION FROM WMS_PARAMETERS_B p, WMS_DB_OBJECT_REFERENCES r WHERE p.DB_OBJECT_REFERENCE_ID = r.DB_OBJECT_REFERENCE_ID;

To list object references that belong to a membership group:

  • SELECT m.DB_OBJECT_REFERENCE_ID, r.DESCRIPTION FROM WMS_DB_OBJ_REF_MEMBERS m, WMS_DB_OBJECT_REFERENCES r WHERE m.DB_OBJECT_REFERENCE_ID = r.DB_OBJECT_REFERENCE_ID;

Common reporting patterns include extracting all user-defined references (USER_DEFINED_FLAG = 'Y'), auditing creation and modification history via the Who columns, and resolving DFF attribute values stored in ATTRIBUTE1–ATTRIBUTE15 for customer-specific tracking. In 12.2.x environments, queries should filter or group by ZD_EDITION_NAME to avoid double-counting records across editions.

Related Objects

The following tables reference or depend on WMS_DB_OBJECT_REFERENCES through documented foreign keys:

  • WMS_DB_OBJ_REF_MEMBERS — References the parent via DB_OBJECT_REFERENCE_ID, defining membership relationships between object references.
  • WMS_PARAMETERS_B — References the parent via DB_OBJECT_REFERENCE_ID, binding parameter definitions to database object references.
  • WMS_DB_OBJECT_REFERENCES_PK — The primary key constraint on DB_OBJECT_REFERENCE_ID.
  • WMS_DB_OBJECT_REFERENCES_U1 — The unique index on DB_OBJECT_REFERENCE_ID and ZD_EDITION_NAME, supporting edition-aware lookups.

Because no outbound foreign keys are documented, this table functions as a parent/reference hub within the WMS metadata model rather than a dependent transactional table.