Results for “wms_device_requests_hist_v”

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

Overview

WMS_DEVICE_REQUESTS_HIST_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Warehouse Management (WMS) product family. Its documented description is "Device Request History," and it exists specifically to support the View Device Request History form. The view exposes the full lifecycle of radio-frequency (RF) device requests issued against warehouse tasks, including the original request, any resubmission, the resulting status and status message, the device that processed the request, and the inventory context (organization, subinventory, locator, item, lot, serial, and LPN) in which the transaction was performed.

Because it consolidates request-level auditing data with descriptive reference data from inventory and device setup, the view serves as a denormalized reporting and integration surface. Rather than joining numerous base objects manually, implementers, report developers, and interface developers can query a single object whose text already resolves lookup meanings, organization codes, concatenated locator flexfield values, item numbers, license plate numbers, and EPC values. This makes it suitable for operational audit reports, device throughput analysis, and troubleshooting of failed or resubmitted RF transactions in both 12.1.1 and 12.2.2 environments.

Underlying Base Objects

The view is defined over the following documented objects:

• WMS_DEVICE_REQUESTS_HIST (SYNONYM, the APPS.WDRH alias) — the driving table holding the historical device request records.
• MFG_LOOKUPS (VIEW, three instances: ML, ML2, ML3) — supplies translated meanings for business event types (WMS_BUS_EVENT_TYPES), task types (WMS_TASK_TYPES, outer-joined), and device types.
• MTL_PARAMETERS (SYNONYM, two instances: MP, MP2) — supplies the organization code for the transaction organization and, via outer join, the transfer organization code.
• MTL_ITEM_LOCATIONS_KFV (VIEW, two instances: MILK1, MILK2) — provides concatenated flexfield segments for the locator and the transfer locator, both outer-joined by organization, subinventory, and locator.
• MTL_SYSTEM_ITEMS (SYNONYM, MSI) — supplies SEGMENT1 as the item number.
• WMS_DEVICES_VL (VIEW, WD) — provides the device name.
• WMS_LICENSE_PLATE_NUMBERS (SYNONYM, WLPN) — supplies the license plate number.
• WMS_EPC (SYNONYM, WE) — supplies the EPC value associated with the request.

The heavy use of outer joins (MP2, MILK1, MILK2, and the lookup instances) means historical rows remain visible even when the transfer organization, transfer locator, or descriptive lookup data has since been removed or is not applicable.

Key Columns

• REQUEST_ID, TASK_ID, RELATION_ID, SEQUENCE_ID — identify the device request and its relationship to the parent warehouse task.
• TASK_SUMMARY, TASK_TYPE_ID, TASK_TYPE — the task narrative and its decoded type.
• BUSINESS_EVENT_ID, BUSINESS_EVENT — the triggering business event and its decoded meaning.
• ORGANIZATION_ID, ORGANIZATION_CODE, SUBINVENTORY_CODE, LOCATOR_ID, LOCATOR — the source inventory context; LOCATOR is the concatenated locator flexfield value.
• TRANSFER_ORG_ID, TRANSFER_ORG_CODE, TRANSFER_SUB_CODE, TRANSFER_LOC_ID, TRANSFER_LOCATOR — the destination context for transfer-type requests, including the concatenated transfer locator.
• INVENTORY_ITEM_ID, ITEM, REVISION, UOM, LOT_NUMBER, LOT_QTY, SERIAL_NUMBER, LPN_ID, LICENSE_PLATE_NUMBER, TRANSACTION_QUANTITY — the item and quantity detail of the transaction.
• DEVICE_ID, DEVICE_NAME, DEVICE_TYPE_ID, DEVICE_TYPE, DEVICE_STATUS — the executing device and its classification.
• STATUS_CODE, STATUS_MSG, OUTFILE_NAME, REASON_ID — request outcome and diagnostic information.
• REQUEST_DATE, RESUBMIT_DATE, CONCURRENT_REQUEST_ID, REQUESTED_BY, RESPONSIBILITY_ID — request timing and the concurrent program/user context.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• XFER_LPN_ID, EPC — the transfer LPN identifier and the EPC value.

Common Use Cases and Queries

A frequent requirement is locating the history of transfers that moved material from one locator to another, which maps directly to the user search term "transfer_locator." The following query lists completed transfer requests with their source and destination locators:

SELECT request_id,
       task_id,
       transfer_locator,
       locator,
       item,
       transaction_quantity,
       device_name,
       status_code,
       request_date
FROM   apps.wms_device_requests_hist_v
WHERE  transfer_locator IS NOT NULL
AND    request_date >= SYSDATE - 7
ORDER  BY request_date DESC;

Other practical scenarios include auditing device throughput by counting requests grouped by DEVICE_NAME and DEVICE_TYPE, tracing failed requests through STATUS_CODE and STATUS_MSG for a given REQUEST_DATE range, and reconciling LPN or EPC movements by filtering on LICENSE_PLATE_NUMBER or EPC. Because the view already decodes lookups and concatenates locator segments, it is well suited as a source for Oracle Reports, BI Publisher data templates, and custom interfaces that need device request history without reconstructing the underlying join logic. For the transfer locator specifically, the presence of TRANSFER_ORG_CODE, TRANSFER_SUB_CODE, and TRANSFER_LOCATOR in a single row allows destination analysis across organization, subinventory, and locator without additional joins, which is particularly useful when validating inter-org and intra-org transfer activity.