Search Results oe_sets_pk

Overview

OE_SETS is a core Order Management (ONT) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores information about line sets. A line set groups related order lines so that fulfillment operations — shipping, arrival scheduling, and lot/serial assignment — can be coordinated as a single logical unit rather than processed line by line. The table is owned by the ONT schema and is documented as VALID in the ETRM repository, where it carries a heuristic Data Vault classification of hub-leaning. From a modeling perspective, this classification suggests treating OE_SETS as a hub entity anchored by its surrogate key, with surrounding transactional context (dates, tolerances, statuses) best modeled as satellites and its relationships to order headers and lines expressed as links.

Key Information Stored

OE_SETS is documented with 23 columns in the 12.2.2 physical schema. The primary key is OE_SETS_PK, defined on SET_ID, which also underlies the unique index OE_SETS_U1. SET_ID is therefore both the surrogate key and the documented business-key candidate for uniquely identifying a set.

The most significant attributes are:

• SET_ID — Surrogate primary key identifying the line set.
• SET_NAME — User-facing name for the set.
• SET_TYPE — Classifies the set (for example, by grouping purpose).
• HEADER_ID — Foreign key to the owning order header in OE_ORDER_HEADERS_ALL.
• SHIP_FROM_ORG_ID and SHIP_TO_ORG_ID — Source and destination organizations for the set.
• SCHEDULE_SHIP_DATE and SCHEDULE_ARRIVAL_DATE — Planned ship and arrival dates driving scheduling.
• FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE, and SHIPMENT_PRIORITY_CODE — Logistics attributes applied to the set.
• SET_STATUS — Current lifecycle status of the set.
• INVENTORY_ITEM_ID, ORDERED_QUANTITY_UOM, and LINE_TYPE_ID — Item, unit of measure, and line type context, with LINE_TYPE_ID referencing PO_LINE_TYPES_B.
• SHIP_TOLERANCE_ABOVE and SHIP_TOLERANCE_BELOW — Permitted over- and under-shipment thresholds.

Standard audit columns (CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE, UPDATE_LOGIN) and INST_ID complete the documented structure.

Common Use Cases and Queries

OE_SETS is typically queried to reconcile sets against their order lines and headers, to report on scheduling and shipping tolerances, and to support fulfillment exception reporting. A representative join retrieves all lines belonging to a set:

• SELECT l.line_id, l.ordered_quantity, s.set_name, s.set_status FROM oe_order_lines_all l, oe_sets s WHERE l.line_set_id = s.set_id AND s.header_id = :header_id;
• SELECT s.set_id, s.set_name, s.schedule_ship_date FROM oe_sets s WHERE s.header_id = :header_id ORDER BY s.schedule_ship_date;
• SELECT s.set_id, COUNT(l.line_id) FROM oe_sets s, oe_order_lines_all l WHERE l.line_set_id = s.set_id GROUP BY s.set_id;

Because OE_ORDER_LINES_ALL references OE_SETS through LINE_SET_ID, SHIP_SET_ID, and ARRIVAL_SET_ID, reporting frequently pivots on those columns to distinguish shipping sets from arrival sets when analyzing delivery consolidation.

Related Objects

The documented foreign-key relationships define the principal related objects:

• 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 primary line-level consumer of set information.
• OE_LINE_SETS — References OE_SETS.SET_ID, providing the line-to-set association.
• OE_LOT_SERIAL_NUMBERS — References OE_SETS.LINE_SET_ID, linking serialized or lot-controlled units to sets.
• PO_LINE_TYPES_B — Referenced via OE_SETS.LINE_TYPE_ID; supplies line type definitions.