Search Results oe_order_lines_pk

Overview

OE_ORDER_LINES_ALL is the core transactional table in the Oracle Order Management (ONT) module, owned by the ONT schema. It stores information for all order lines across Oracle EBS 12.1.1 and 12.2.2. Every sales order line — whether a standard shippable item, a return, a configured model, an option, or a service line — is persisted here with its pricing, scheduling, fulfillment, shipping, and reference attributes. The table contains 362 documented columns in ETRM 12.2.2, making it one of the widest and most heavily integrated transactional entities in the E-Business Suite.

From a Data Vault modeling perspective, the metadata classifies this object as hub-leaning. This suggests it functions primarily as a durable business-key repository around which satellite tables (such as OE_ORDER_LINES_HISTORY and OE_ORDER_PRICE_ATTRIBS) and link tables capturing hierarchical and cross-module relationships are organized.

Key Information Stored

The surrogate primary key is LINE_ID, enforced by the unique index OE_ORDER_LINES_U1 and the constraint OE_ORDER_LINES_PK. The most operationally significant columns include:

• HEADER_ID — foreign key to OE_ORDER_HEADERS_ALL, tying each line to its parent order.
• LINE_NUMBER and SHIPMENT_NUMBER — the business-visible line and shipment identifiers.
• ORDERED_ITEM and INVENTORY_ITEM_ID — the ordered item code and its internal item reference.
• ORDERED_QUANTITY, SHIPPED_QUANTITY, CANCELLED_QUANTITY, FULFILLED_QUANTITY, and INVOICED_QUANTITY — the quantity lifecycle across the order-to-cash flow.
• UNIT_SELLING_PRICE, UNIT_LIST_PRICE, and PRICE_LIST_ID — pricing data, with PRICE_LIST_ID referencing QP_LIST_HEADERS_B.
• REQUEST_DATE, PROMISE_DATE, SCHEDULE_SHIP_DATE, and EARLIEST_SHIP_DATE — scheduling and ATP-related dates.
• FLOW_STATUS_CODE — the workflow status driving line-level processing.
• LINE_TYPE_ID — references OE_TRANSACTION_TYPES_ALL and governs line behavior.
• TOP_MODEL_LINE_ID, ATO_LINE_ID, LINK_TO_LINE_ID, and SPLIT_FROM_LINE_ID — self-referencing columns for configured models, options, and line splits.
• OPEN_FLAG, BOOKED_FLAG, and CANCELLED_FLAG — status indicators frequently used in reporting.

Common Use Cases and Queries

OE_ORDER_LINES_ALL underpins order status reporting, backlog analysis, fulfillment tracking, and integration extracts. A typical query joining lines to headers for open orders is:

• SELECT h.ORDER_NUMBER, l.LINE_NUMBER, l.ORDERED_ITEM, l.ORDERED_QUANTITY, l.FLOW_STATUS_CODE FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL l WHERE h.HEADER_ID = l.HEADER_ID AND l.OPEN_FLAG = 'Y' AND l.ORG_ID = :org_id;
• Pricing audits leverage UNIT_SELLING_PRICE and UNIT_LIST_PRICE against PRICE_LIST_ID to compute discount percentages.
• Configured-item analysis traverses TOP_MODEL_LINE_ID and ATO_LINE_ID to reconstruct model-option hierarchies.
• Backlog and shipped-not-invoiced reports compare SHIPPED_QUANTITY against INVOICED_QUANTITY.
• Integration and interface programs commonly filter on FLOW_STATUS_CODE or BOOKED_FLAG to extract new or changed lines.

Related Objects

The table participates in an extensive foreign-key network. Significant related objects include:

• OE_ORDER_HEADERS_ALL — joined via HEADER_ID; the parent order record.
• OE_ORDER_LINES_HISTORY — references LINE_ID and stores audit history of line changes.
• OE_ORDER_HOLDS_ALL — references LINE_ID for credit and fulfillment holds.
• OE_PRICE_ADJUSTMENTS and OE_ORDER_PRICE_ATTRIBS — reference LINE_ID for pricing detail and attribute snapshots.
• OE_SALES_CREDITS — references LINE_ID for salesperson credit assignment.
• OE_TRANSACTION_TYPES_ALL and OE_ORDER_SOURCES — referenced by LINE_TYPE_ID and SOURCE_DOCUMENT_TYPE_ID respectively.
• OE_SETS — referenced by SHIP_SET_ID, LINE_SET_ID, and ARRIVAL_SET_ID for shipment grouping.
• OE_LOT_SERIAL_NUMBERS, OE_DROP_SHIP_SOURCES, and OE_LINE_ACKS — child tables keyed by LINE_ID supporting serialization, drop-ship, and acknowledgment processing.
• MTL_CUSTOMER_ITEMS and QP_LIST_HEADERS_B — referenced by ORDERED_ITEM_ID and PRICE_LIST_ID.