Search Results inventory_location_segments




Overview
Explore in AI Chat

Overview

The SO_PICKING_LINE_DETAIL_VO_V view is a reporting and integration construct in the Oracle E-Business Suite Order Entry (OE) module, owned by the APPS schema. It consolidates picking line detail records with item master, organization, and warehouse freight attributes, exposing a denormalized, query-ready dataset for downstream consumers such as Oracle Shipping Execution, mobile picking applications, and custom operational reports. The view is defined with a SELECT DISTINCT clause, which suppresses duplicate rows arising from the joins across item, organization, and freight metadata.

A defining characteristic of this view is the runtime invocation of the OE_QUERY.LOT_EXPIRATION packaged function to derive the LOT_EXPIRATION_DATE column. Because lot expiration is not stored on the picking line detail record itself, the view synthesizes it dynamically from the item, warehouse, and lot number. The inclusion of this derived column makes the view the primary reference point for users and developers searching on lot_expiration_date in the Pick Confirmation and Shipping Execution workflows, where lot-controlled items require expiry visibility during the pick, pack, and ship cycle.

Underlying Base Objects

The view is defined over the following documented base objects:

  • SO_PICKING_LINE_DETAILS (SYNONYM) — the primary driving table, aliased SPLD, supplying picking line detail identifiers, quantities, lot, revision, subinventory, locator segments, and descriptive flexfield attributes.
  • SO_PICKING_LINES (VIEW) — aliased SPL, joined on PICKING_LINE_ID, providing the inventory item identifier, unit code, and ship method code.
  • MTL_SYSTEM_ITEMS_VL (VIEW) — aliased MSI, joined on both INVENTORY_ITEM_ID and ORGANIZATION_ID, supplying the translated item description.
  • MTL_PARAMETERS (SYNONYM) — aliased MP, joined with an outer join on WAREHOUSE_ID, returning the organization code.
  • ORG_FREIGHT (SYNONYM) — aliased ORGF, outer-joined through FND_PROFILE.VALUE('SO_ORGANIZATION_ID') to resolve freight context.
  • OE_QUERY (PACKAGE) — the PL/SQL package supplying the LOT_EXPIRATION function.
  • FND_PROFILE (PACKAGE) — provides the runtime organization context through the profile option lookup.

The outer joins on MTL_PARAMETERS and ORG_FREIGHT ensure that detail rows are not lost when warehouse or freight configuration records are absent.

Key Columns

  • LOT_EXPIRATION_DATE — derived at query time by OE_QUERY.LOT_EXPIRATION(INVENTORY_ITEM_ID, WAREHOUSE_ID, LOT_NUMBER); the column most frequently referenced for expiry-driven picking, FEFO (first-expiry-first-out) logic, and shelf-life validation.
  • PICKING_LINE_DETAIL_ID / PICKING_LINE_ID — primary and foreign key linkage back to the picking entities.
  • INVENTORY_ITEM_ID / ITEM_DESCRIPTION — item identification and translated description.
  • REQUESTED_QUANTITY — the quantity to be picked for the detail line.
  • LOT_NUMBER / REVISION — lot and revision control attributes for the allocated stock.
  • WAREHOUSE_ID / WAREHOUSE — internal organization identifier and its user-facing organization code.
  • SUBINVENTORY / INVENTORY_LOCATION_SEGMENTS / INVENTORY_LOCATION_ID — material storage location hierarchy.
  • SEGMENT1 … SEGMENT20 — the concatenated inventory location segment values.
  • DETAIL_TYPE_CODE / RELEASED_FLAG / AUTOSCHEDULED_FLAG / SCHEDULE_DATE — status and scheduling attributes governing release and pick execution.
  • ATTRIBUTE1 … ATTRIBUTE15 / CONTEXT — descriptive flexfield context and segments.
  • ROW_ID — the base table ROWID, exposed for programmatic updates.

Common Use Cases and Queries

Typical uses include expiry-based pick planning, aging analysis of allocated lots, and integration feeds into warehouse management or mobile devices. A representative query returns picking details with expiry information for a specific warehouse:

  • SELECT picking_line_detail_id, picking_line_id, inventory_item_id, item_description, lot_number, lot_expiration_date, requested_quantity, subinventory FROM apps.so_picking_line_detail_vo_v WHERE warehouse_id = :p_warehouse_id AND released_flag = 'Y';
  • SELECT lot_number, lot_expiration_date, SUM(requested_quantity) qty FROM apps.so_picking_line_detail_vo_v WHERE inventory_item_id = :p_item_id GROUP BY lot_number, lot_expiration_date ORDER BY lot_expiration_date;
  • SELECT picking_line_detail_id, lot_expiration_date FROM apps.so_picking_line_detail_vo_v WHERE lot_expiration_date < SYSDATE + 30;

Because LOT_EXPIRATION_DATE is computed row-by-row through a PL/SQL function call, queries filtering or ordering on this column can carry performance overhead on large pick batches; restricting by item, warehouse, or picking line first is advisable before applying expiry predicates.