Search Results wms_device_assignment_temp




Overview
Explore in AI Chat

Overview

WMS.WMS_DEVICE_ASSIGNMENT_TEMP is a Warehouse Management (WMS) temporary (staging) table used during the device assignment process in Oracle E-Business Suite. It serves as an interim holding area where rows representing candidate or pending assignments of warehouse equipment, devices, and equipment instances to employees are inserted, validated, and subsequently processed into their permanent home. In Oracle EBS 12.1.1 and 12.2.2 the object is documented as VALID with the WMS owner and carries the primary key constraint WMS_DEVICE_ASSIGNMENT_TEMP_PK on the column ASSIGNMENT_TEMP_ID. Because it is a transient workspace rather than a transactional master, its rows typically persist only for the duration of an assignment or staging cycle.

From a Data Vault modeling perspective, the ETRM metadata classifies this object heuristically as standalone. That designation suggests treating it as a satellite-like or staging structure in a dimensional or Data Vault model, rather than as a central hub or a link between two business entities. In practice, this reflects the table's role as an operational buffer whose foreign key linkage extends to employee data rather than to a broad web of master entities.

Key Information Stored

The documented physical schema comprises eleven columns. The most operationally significant are:

  • ASSIGNMENT_TEMP_ID — the surrogate primary key (WMS_DEVICE_ASSIGNMENT_TEMP_PK) and the sole unique index candidate (WMS_DEVICE_ASSIGN_TEMP_U1), uniquely identifying each staged assignment row.
  • EMPLOYEE_ID — the worker to whom the device or equipment is being assigned; this is the documented foreign key referencing PSB_EMPLOYEES.
  • ORGANIZATION_ID — the inventory or warehouse organization under which the assignment is scoped, enabling multi-organization filtering and reporting.
  • EQUIPMENT_ID — the equipment master identifier being assigned.
  • EQUIPMENT_INSTANCE_ID — the specific serialized instance of that equipment, distinguishing individual physical units.
  • DEVICE_ID — the device or radio-frequency device record associated with the assignment.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard Oracle EBS WHO-audit columns capturing creation and last-modification context, essential for purge logic and audit traceability on temporary data.

Common Use Cases and Queries

Typical uses include validating a batch of device assignments before committing them to the permanent assignment tables, reviewing staged rows per warehouse organization, and reconciling employee-to-equipment allocations prior to posting. A representative query joining the documented employee foreign key is:

  • SELECT t.assignment_temp_id, t.employee_id, e.employee_number, t.organization_id, t.equipment_id, t.equipment_instance_id, t.device_id FROM wms.wms_device_assignment_temp t LEFT JOIN psb.psb_employees e ON e.employee_id = t.employee_id WHERE t.organization_id = :org_id;
  • Purge or reconciliation of stale staging rows: SELECT assignment_temp_id, creation_date, created_by FROM wms.wms_device_assignment_temp WHERE creation_date < SYSDATE - 1;
  • Reporting on pending assignments grouped by organization and device to detect duplicate or orphaned staging records.

Related Objects

The documented relationship is narrow: EMPLOYEE_ID references PSB_EMPLOYEES, the primary dependency for resolving worker details. Because the metadata classifies the object as standalone, no other foreign keys are documented. Practically, the table is associated with the permanent WMS device assignment structures it feeds and with the equipment and device masters referenced by EQUIPMENT_ID, EQUIPMENT_INSTANCE_ID, and DEVICE_ID, along with standard inventory organization context supplied by ORGANIZATION_ID. Consumers should treat WMS_DEVICE_ASSIGNMENT_TEMP as an input-to-final assignment process rather than as a durable reporting source.