Search Results oe_sets_u1

Overview

ONT.OE_SETS is a transactional table within the Oracle E-Business Suite Order Management (ONT) schema that stores information on line sets — groupings of order lines that are shipped, scheduled, or fulfilled together. In Oracle EBS 12.1.1 and 12.2.2, line sets support shipping consolidation, arrival scheduling, and fulfillment logic, allowing multiple order lines to be treated as a single logistical unit rather than as independent shipments.

The object resides in the APPS_TS_TX_DATA tablespace and is registered as FND Design Data ONT.OE_SETS with a status of VALID. It is a high-volume transactional table that participates in the core order fulfillment data model.

From a dimensional modeling perspective, the Data Vault classification for this object is heuristic hub-leaning. This suggests it may best be modeled as a hub entity keyed on its primary key (SET_ID), with related descriptive attributes potentially distributed into satellite structures in a downstream warehouse. This is a modeling suggestion rather than a prescriptive requirement.

Key Information Stored

OE_SETS contains 23 documented columns. The most significant include:

• SET_ID — The surrogate primary key (OE_SETS_PK) and the column enforced by the unique index OE_SETS_U1. This is the definitive identifier for a line set.
• SET_NAME — User-defined name for the set; part of the nonunique index OE_SETS_N1 and N2, making it a business-key candidate for lookup and reporting.
• SET_TYPE — Indicates the type of set (Ship, Line, Arrival, or Fulfillment), which determines how the set is processed in fulfillment.
• HEADER_ID — Foreign key to OE_ORDER_HEADERS_ALL, identifying the order header that owns the set.
• SHIP_FROM_ORG_ID and SHIP_TO_ORG_ID — Warehouse and ship-to organization identifiers.
• SCHEDULE_SHIP_DATE and SCHEDULE_ARRIVAL_DATE — Scheduled ship and arrival dates for the set.
• FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE, SHIPMENT_PRIORITY_CODE — Carrier, shipping method, and priority for the set.
• SET_STATUS — Indicates whether the set is open or closed.
• INVENTORY_ITEM_ID and ORDERED_QUANTITY_UOM — Item and unit of measure associated with a line set.
• LINE_TYPE_ID — Foreign key to PO_LINE_TYPES_B defining the line type for a line set.
• SHIP_TOLERANCE_ABOVE and SHIP_TOLERANCE_BELOW — Over- and under-shipment/return tolerances.
• INST_ID — Used for RAC instance affinity and context setting.

Standard Who columns (CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE, UPDATE_LOGIN) provide audit tracking. The distinction between the surrogate key (SET_ID) and business-key candidates (SET_NAME) is significant for integration and reconciliation work.

Common Use Cases and Queries

OE_SETS is queried heavily during order fulfillment, shipment consolidation, and arrival set processing. Typical scenarios include retrieving all sets belonging to an order header, identifying open sets by ship date, and reconciling line sets to their constituent order lines.

Sample query to list open ship sets for an order:

• SELECT set_id, set_name, set_type, schedule_ship_date FROM oe_sets WHERE header_id = :p_header_id AND set_status = 'OPEN' AND set_type = 'SHIP';

Reporting by shipping method and carrier:

• SELECT shipping_method_code, freight_carrier_code, COUNT(*) FROM oe_sets GROUP BY shipping_method_code, freight_carrier_code;

Joining to order lines to enumerate set membership:

• SELECT s.set_id, s.set_name, l.line_id, l.ordered_quantity FROM oe_sets s, oe_order_lines_all l WHERE l.line_set_id = s.set_id AND s.set_type = 'LINE';

Preferred access paths align with available indexes: queries filtering on SET_ID use OE_SETS_U1; filters on HEADER_ID, SET_NAME, and SET_TYPE leverage OE_SETS_N1; SET_NAME lookups use OE_SETS_N2.

Related Objects

OE_SETS sits at the center of a hub-like relationship cluster. The most significant related objects are:

• OE_ORDER_HEADERS_ALL — Referenced via OE_SETS.HEADER_ID; the parent order header.
• OE_ORDER_LINES_ALL — References OE_SETS through SHIP_SET_ID, LINE_SET_ID, and ARRIVAL_SET_ID; the principal child holding order lines.
• OE_LINE_SETS — References OE_SETS via SET_ID, linking sets to individual lines.
• OE_LOT_SERIAL_NUMBERS — References OE_SETS via LINE_SET_ID for lot and serial tracking.
• PO_LINE_TYPES_B — Referenced via OE_SETS.LINE_TYPE_ID, supplying line type definitions.
• MTL_SYSTEM_ITEMS_B / MTL_PARAMETERS — Implied lookups for INVENTORY_ITEM_ID and organization IDs used in shipping fields.

Together these relationships position OE_SETS as a central grouping entity within Order Management fulfillment processing, and any purge or archival activity on this table should account for the dependent child references in OE_ORDER_LINES_ALL and OE_LINE_SETS.