Search Results oe_lot_serial_numbers




Overview
Explore in AI Chat

Overview

The OE_LOT_SERIAL_NUMBERS table is an Order Management (ONT) transactional entity that captures customer-reported lot and serial numbers associated with lot- and/or serial-controlled return lines. When a customer returns a controlled item, the specific lot numbers, serial number ranges, or sublots being returned must be recorded against the return order line so that downstream receiving, inventory, and quality processes can reconcile the physical goods against the original shipment. This table is the repository for that captured data. It resides in the ONT schema and is fully valid in both Oracle EBS 12.1.1 and 12.2.2, with the documented physical schema in 12.2.2 comprising 33 columns.

Under the heuristic Data Vault classification mined from its foreign key structure, OE_LOT_SERIAL_NUMBERS is best modeled as a link table. It does not behave as a standalone hub because it has no independent business identity; instead it associates a controlled detail (a lot/serial entry) with two parent entities: an order line (via LINE_ID) and an order line set (via LINE_SET_ID). This dual-parent relationship, combined with descriptive attributes such as lot number, serial range, and quantity, gives the table its characteristic link-plus-attribute structure.

Key Information Stored

The primary surrogate key is LOT_SERIAL_ID, which uniquely identifies each lot/serial record. The principal foreign key and business-linkage column is LINE_ID, which points to the parent order line in OE_ORDER_LINES_ALL; a second foreign key, LINE_SET_ID, references OE_SETS. The operational payload of the record is carried by LOT_NUMBER (the reported lot), FROM_SERIAL_NUMBER and TO_SERIAL_NUMBER (a contiguous serial range, or an individual serial when the two are equal), SUBLOT_NUMBER for sublot-controlled items, and QUANTITY / QUANTITY2, which express the returned quantity for the lot or serial lines. The column ORIG_SYS_LOTSERIAL_REF preserves the originating system's lot/serial reference, supporting integration and order-import scenarios where external systems supply the controlled values.

The table retains standard EBS audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — along with the INST_ID multi-org installation identifier and LOCK_CONTROL, which governs concurrent update behavior. A fifteen-column ATTRIBUTE1 through ATTRIBUTE15 descriptive flexfield block, headed by CONTEXT, allows customers to extend the table without modification. Business-key candidates, where a unique index exists, are typically the combination of LINE_ID plus LOT_NUMBER plus the FROM_SERIAL_NUMBER/TO_SERIAL_NUMBER range rather than the surrogate LOT_SERIAL_ID.

Common Use Cases and Queries

Typical scenarios include generating return authorization reports, validating that customer-declared serials match shipped inventory, and feeding receiving transactions with pre-populated lot/serial data. A common query pattern joins this table to its parent order line:

  • List all lot/serial records for a given return line: SELECT * FROM OE_LOT_SERIAL_NUMBERS WHERE LINE_ID = :line_id.
  • Correlate with order header data: SELECT l.line_number, s.lot_number, s.from_serial_number, s.to_serial_number, s.quantity FROM OE_LOT_SERIAL_NUMBERS s, OE_ORDER_LINES_ALL l WHERE s.line_id = l.line_id AND l.header_id = :header_id.
  • Reporting on lot-controlled returns by lot: SELECT lot_number, SUM(quantity) FROM OE_LOT_SERIAL_NUMBERS GROUP BY lot_number.
  • Tracing external references via ORIG_SYS_LOTSERIAL_REF for import reconciliation.

Related Objects

The most significant objects related to OE_LOT_SERIAL_NUMBERS, grounded in its foreign key structure, are:

  • OE_ORDER_LINES_ALL — parent order line; joined on OE_LOT_SERIAL_NUMBERS.LINE_ID = OE_ORDER_LINES_ALL.LINE_ID.
  • OE_SETS — the order line set; joined on OE_LOT_SERIAL_NUMBERS.LINE_SET_ID = OE_SETS.LINE_SET_ID.
  • OE_ORDER_HEADERS_ALL — reached via the order line to obtain header-level context.
  • MTL_LOT_NUMBERS and MTL_SERIAL_NUMBERS — inventory master tables used to validate reported values.
  • RSD (receiving) and Inventory transaction interfaces that consume the captured data during return receipt.