Search Results wms_device_assign_temp_u1

Overview

WMS.WMS_DEVICE_ASSIGNMENT_TEMP is a transient Device Management table in the Warehouse Management System (WMS) schema of Oracle E-Business Suite. It records the devices that users are currently signed on to, functioning as a runtime session registry rather than a permanent master data store. The table captures the association between an employee, an organization, a mobile or RF device, and the equipment (inventory item and serial instance) being operated. The ASSIGNMENT_TEMP_ID column establishes the sequence in which devices were signed on. Records are purged automatically once the user signs off the device, which means the table contents reflect only active, live assignments at any point in time.

From a data modeling perspective, the mined relationship structure suggests classifying this table as a standalone or hub-style object. Its foreign key to PSB_EMPLOYEES on EMPLOYEE_ID anchors it to the employee dimension, but the absence of outbound references to other WMS transactional tables indicates it is a lean, self-contained registry rather than a link or satellite within a broader Data Vault model. This is consistent with its temporary, session-oriented purpose.

Key Information Stored

The primary key and unique-index candidate is ASSIGNMENT_TEMP_ID, indexed by the unique index WMS_DEVICE_ASSIGN_TEMP_U1. This surrogate key also encodes the sign-on order of devices. The most significant business columns include:

• ASSIGNMENT_TEMP_ID — Surrogate device assignment identifier and unique index column (WMS_DEVICE_ASSIGN_TEMP_U1); drives ordering of device sign-ons.
• EMPLOYEE_ID — The employee currently signed on; non-unique index WMS_DEVICE_ASSIGN_TEMP_N1 supports lookups by employee, and an FK references PSB_EMPLOYEES.
• ORGANIZATION_ID — The inventory organization context for the assignment.
• EQUIPMENT_ID — Inventory item identifier of the equipment being used to perform the task.
• EQUIPMENT_INSTANCE_ID — Serial number (VARCHAR2(30)) of the specific equipment instance in use.
• DEVICE_ID — Identifier of the physical device signed on.
• CREATION_DATE / CREATED_BY / LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — Standard WHO audit columns tracking record lifecycle and the acting user.

The business-key candidate is ASSIGNMENT_TEMP_ID via the unique index; EMPLOYEE_ID is the principal non-unique search attribute. No composite business key is documented.

Common Use Cases and Queries

Operational use cases center on real-time visibility into which devices are actively assigned to which employees, and to which equipment. Warehouse supervisors query this table to identify free or occupied devices, reconcile device-to-employee mapping, and troubleshoot sign-on conflicts. Because rows are removed on sign-off, reporting typically targets current-state dashboards.

Typical queries include:

• Listing active assignments by employee: SELECT * FROM WMS.WMS_DEVICE_ASSIGNMENT_TEMP WHERE EMPLOYEE_ID = :emp_id ORDER BY ASSIGNMENT_TEMP_ID;
• Counting devices in use per organization: SELECT ORGANIZATION_ID, COUNT(*) FROM WMS.WMS_DEVICE_ASSIGNMENT_TEMP GROUP BY ORGANIZATION_ID;
• Correlating equipment serial numbers to devices: SELECT DEVICE_ID, EQUIPMENT_ID, EQUIPMENT_INSTANCE_ID FROM WMS.WMS_DEVICE_ASSIGNMENT_TEMP;
• Auditing recent sign-ons: SELECT EMPLOYEE_ID, DEVICE_ID, CREATION_DATE FROM WMS.WMS_DEVICE_ASSIGNMENT_TEMP ORDER BY CREATION_DATE DESC;

Related Objects

The most significant relationship is to PSB_EMPLOYEES via EMPLOYEE_ID, which resolves the assigned operator's identity. Other functionally relevant objects include the WMS device master (WMS_DEVICES) joined on DEVICE_ID, inventory item and serial tables joined on EQUIPMENT_ID and EQUIPMENT_INSTANCE_ID, and WMS equipment/resource definitions that interpret the equipment context. The confirmed dependency list shows WMS_DEVICE_ASSIGNMENT_TEMP is referenced by the WMS synonym or view WMS_DEVICE_ASSIGNMENT_TEMP#. Because the table is standalone and does not reference other database objects beyond the employee FK, joins to external tables are driven by functional business keys rather than declared constraints. This reinforces its role as a lightweight, session-scoped registry supporting WMS mobile and RF device management.