Search Results wms_db_obj_ref_members




Overview
Explore in AI Chat

Overview

The WMS_DB_OBJ_REF_MEMBERS table is a core metadata repository within the Oracle E-Business Suite Warehouse Management (WMS) module. It functions as a junction or mapping table that defines the specific member objects included within a database object reference. In essence, it establishes a detailed, ordered list of the individual database objects (such as tables, views, or packages) that collectively constitute a larger logical reference group. This structure is critical for managing complex warehouse configurations, data validations, and batch processing logic where operations must be performed on a defined, sequenced set of underlying database entities. Its role is foundational to the WMS module's ability to dynamically reference and process groups of related objects in versions 12.1.1 and 12.2.2.

Key Information Stored

The table's primary purpose is to store membership and sequence information for database object references. While the full column list is not detailed in the provided excerpt, the defined constraints reveal the critical data elements. The composite primary key consists of DB_OBJECT_REFERENCE_ID and SEQUENCE_NUMBER. The DB_OBJECT_REFERENCE_ID is a foreign key linking to the WMS_DB_OBJECT_REFERENCES table, identifying the parent reference group. The SEQUENCE_NUMBER column dictates the order of the members within that group, which is vital for processes requiring sequential execution or evaluation. The DB_OBJECT_ID is a foreign key linking to the WMS_DB_OBJECTS table, uniquely identifying the specific database object (the member) being included in the reference.

Common Use Cases and Queries

This table is primarily accessed for system configuration, troubleshooting, and generating audit reports on WMS object dependencies. A common use case involves identifying all members of a specific object reference for impact analysis before a database change. For example, to list all member objects for a known reference ID in sequence order, a developer or DBA might execute:

  • SELECT wm.SEQUENCE_NUMBER, wo.OBJECT_NAME
    FROM WMS_DB_OBJ_REF_MEMBERS wm,
    WMS_DB_OBJECTS wo
    WHERE wm.DB_OBJECT_REFERENCE_ID = :ref_id
    AND wm.DB_OBJECT_ID = wo.DB_OBJECT_ID
    ORDER BY wm.SEQUENCE_NUMBER;

Another key scenario is validating the integrity of reference definitions by checking for orphaned member records or ensuring sequence numbers are contiguous within a reference group.

Related Objects

The WMS_DB_OBJ_REF_MEMBERS table sits at the intersection of two primary WMS metadata tables, as defined by its foreign key relationships:

  • WMS_DB_OBJECT_REFERENCES: The parent table defining the reference group. WMS_DB_OBJ_REF_MEMBERS.DB_OBJECT_REFERENCE_ID references this table to associate each member row with its parent reference.
  • WMS_DB_OBJECTS: The master table of database objects known to the WMS module. WMS_DB_OBJ_REF_MEMBERS.DB_OBJECT_ID references this table to identify the specific table, view, or other object that is a member of the reference.

These relationships form a hierarchical metadata model: a DB_OBJECT_REFERENCE contains an ordered list of DB_OBJECTS, with the WMS_DB_OBJ_REF_MEMBERS table providing the essential link that defines the membership and sequence.