Search Results inventory_item




Overview
Explore in AI Chat

Overview

WMS_LPN_CONTENTS_SERIAL_V is an Oracle E-Business Suite (EBS) database view owned by the APPS schema and defined in the Warehouse Management (WMS) product module. It presents the contents of License Plate Numbers (LPNs) — the physical containers, pallets, and cartons tracked by Oracle Warehouse Management — but restricts its output to inventory items that are serial controlled. In Oracle EBS 12.1.1 and 12.2.2, this view is a reporting and integration construct rather than a transactional entity; it exposes a denormalized, read-only projection that joins LPN header data with serial number records and item master attributes. Because serial-controlled material requires unit-level traceability, the view serves as a convenient access point for queries that must reconcile a container with the specific serial numbers it holds, along with the item's control attributes such as lot control and locator control. The view text is documented in the ETRM repository as valid and implemented under APPS, and it is referenced throughout warehouse reporting, label printing, and third-party integration logic where serialized container contents must be resolved without navigating the base tables directly.

Underlying Base Objects

The view is defined over three documented base objects, all exposed to APPS as synonyms:

  • MTL_SERIAL_NUMBERS — aliased as WLC. This is the driving table, supplying serial number, revision, lot number, current status, cost group, and the current organization and locator identifiers for each serialized unit.
  • WMS_LICENSE_PLATE_NUMBERS — aliased twice as WLP and WLP1. The first instance joins on the LPN identifier to return the parent license plate number, organization, locator, subinventory, and the outermost LPN identifier. The second instance resolves the outermost LPN identifier to its license plate number via the condition WLP.OUTERMOST_LPN_ID = WLP1.LPN_ID.
  • MTL_SYSTEM_ITEMS_KFV — aliased as MSI. The key flexfield view of the item master, joined on organization and inventory item identifiers, supplying the padded concatenated item segments, description, primary unit of measure, lot control code, and location control code.

The join is an inner join throughout. A serial number only appears when its LPN_ID resolves to a valid license plate record, and item attributes are only returned when the item exists in the organization recorded on the serial number. This means the view reports serialized contents strictly where full LPN, serial, and item context coexist.

Key Columns

Common Use Cases and Queries

Typical scenarios include auditing the serialized contents of a specific container, tracing which outermost pallet holds a given serial number, and validating that items with specific locator control settings are correctly placed. A representative query returns all serialized contents of a parent LPN:

  • SELECT parent_license_plate_number, serial_number, inventory_item, current_status FROM wms_lpn_contents_serial_v WHERE parent_license_plate_number = :lpn;

A second pattern lists serialized contents filtered by locator control, directly using the column that matches the search term:

  • SELECT outermost_license_plate_number, inventory_item, item_locator_control, current_locator_id FROM wms_lpn_contents_serial_v WHERE item_locator_control = 2;

Because the view is read-only and denormalized, it is well suited to ad hoc reporting, custom concurrent programs, and integration extracts. Performance depends on the underlying joins to MTL_SERIAL_NUMBERS and the license plate tables, so queries should filter on indexed columns such as LPN identifiers or organization and item identifiers wherever possible.