Search Results so_lines_pk

Overview

SO_LINES_ALL is the core Order Entry (OE) transactional table in Oracle E-Business Suite, holding every order line belonging to a sales order header. It resides in the OE schema and is documented as VALID in both EBS 12.1.1 and 12.2.2. In the Order-to-Cash flow, SO_LINES_ALL sits between SO_HEADERS_ALL (the order header) and downstream shipping, pricing, invoicing, and manufacturing objects. Each row represents a single ordered item, service, configuration component, or return line.

The table is wide: ETRM documents 241 columns in 12.2.2. It carries an enormous number of foreign keys, both self-referential (parent/child line structures, ATO configuration hierarchies, service lineages) and cross-module (pricing, terms, commitments, BOM components, customer items). Based on the FK structure mined from the metadata, the heuristic Data Vault classification is hub-leaning. In Data Vault modeling terms, SO_LINES_ALL behaves as a hub anchored by its business/surrogate key LINE_ID, though the pervasive attribute columns (quantities, prices, dates, statuses) mean a satellite would typically be attached to capture the descriptive history.

Key Information Stored

The table's surrogate primary key is LINE_ID, enforced by unique index SO_LINES_U1 / constraint SO_LINES_PK. The most operationally significant columns include:

• HEADER_ID — foreign key back to SO_HEADERS_ALL, linking the line to its parent order.
• LINE_NUMBER — the user-visible line sequence within the order.
• INVENTORY_ITEM_ID — the ordered item; relates to CUSTOMER_ITEM_ID and MTL_CUSTOMER_ITEMS.
• LINE_TYPE_CODE and ITEM_TYPE_CODE — classify the line (e.g., standard, service, return, configuration).
• ORDERED_QUANTITY, SHIPPED_QUANTITY, CANCELLED_QUANTITY, INVOICED_QUANTITY, QUANTITY_TO_INVOICE — the quantity lifecycle.
• SELLING_PRICE, LIST_PRICE, UNIT_CODE, PRICE_LIST_ID, PRICING_METHOD_CODE — pricing and currency detail.
• SHIP_TO_SITE_USE_ID, SHIP_TO_CONTACT_ID, WAREHOUSE_ID, SHIP_METHOD_CODE, SHIPMENT_PRIORITY_CODE — shipping and delivery routing.
• SCHEDULE_DATE, PROMISE_DATE, DATE_REQUESTED_CURRENT, LATEST_ACCEPTABLE_DATE — scheduling and demand dates.
• OPEN_FLAG and OPEN_FLAG/WH_UPDATE_DATE — line status relative to fulfillment.
• ORG_ID — multi-org operating unit discriminator.
• Self-referencing hierarchy columns: PARENT_LINE_ID, ATO_LINE_ID, LINK_TO_LINE_ID, SHIPMENT_SCHEDULE_LINE_ID, SERVICE_PARENT_LINE_ID.
• Flexfield and audit columns: CONTEXT/ATTRIBUTE1–ATTRIBUTE15, CREATION_DATE, LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical reporting joins the line back to the header and outward to shipping and invoicing:

• Order line detail for a given order: SELECT * FROM SO_LINES_ALL WHERE HEADER_ID = :p_header_id ORDER BY LINE_NUMBER;
• Open lines by item: filter OPEN_FLAG = 'Y' and ORDERED_QUANTITY > NVL(SHIPPED_QUANTITY,0).
• Backorder/promise-date analysis joined to SO_HEADERS_ALL and inventory.
• Serial/configuration drill-down via ATO_LINE_ID and PARENT_LINE_ID self-joins.
• Price/quantity reconciliation against RA_CUSTOMER_TRX_LINES_ALL using CREDIT_INVOICE_LINE_ID.

Related Objects

The most significant related objects and their join columns are:

• SO_HEADERS_ALL — joined on HEADER_ID (parent order).
• SO_LINES_ALL (self) — joined on PARENT_LINE_ID, ATO_LINE_ID, SHIPMENT_SCHEDULE_LINE_ID, SERVICE_PARENT_LINE_ID, LINK_TO_LINE_ID.
• SO_PICKING_LINES_ALL — reference via ORDER_LINE_ID for fulfillment.
• RA_CUSTOMER_TRX_ALL / RA_CUSTOMER_TRX_LINES_ALL — COMMITMENT_ID, CREDIT_INVOICE_LINE_ID.
• BOM_INVENTORY_COMPONENTS / BOM_BILL_OF_MATERIALS — COMPONENT_SEQUENCE_ID for configured items.
• SO_PRICE_ADJUSTMENTS — LINE_ID for discounts and modifiers.
• SO_ORDER_HOLDS_ALL and SO_LINE_APPROVALS — LINE_ID for holds and approvals.
• SO_DROP_SHIP_SOURCES — LINE_ID for drop-ship lines.
• MTL_CUSTOMER_ITEMS — CUSTOMER_ITEM_ID for customer part references.