Search Results po_lines_all

Overview

OE_DROP_SHIP_SOURCES is a core Order Management (ONT) transaction table within Oracle E-Business Suite 12.1.1 and 12.2.2. It persists the linkage between order lines in the sales order model and the corresponding procurement documents created to fulfill them through a drop-ship or back-to-back arrangement. When a sales order line is sourced directly from a supplier rather than from inventory, Oracle Order Management automatically generates a requisition and, upon approval, a purchase order. OE_DROP_SHIP_SOURCES records this sourcing relationship so that the sales order line, purchasing requisition line, and purchase order line remain synchronized throughout order fulfillment and receipt.

The table is owned by the ONT schema and, in the documented 12.2.2 physical schema, contains 17 columns. It is a narrow, transaction-oriented bridge table whose primary purpose is referential association rather than descriptive attribute storage. Under the heuristic Data Vault classification derived from its foreign key structure, OE_DROP_SHIP_SOURCES is best modeled as a link table. It resolves a many-to-one (or, in re-sourcing scenarios, effectively one-to-many) relationship between sales order entities and their procurement counterparts, and it should not be treated as a hub or satellite in a dimensional model.

Key Information Stored

The table's key information revolves around two clusters of identifiers: the sales order side and the purchasing side, plus the surrogate key that uniquely identifies each sourcing record.

• DROP_SHIP_SOURCE_ID — the system-generated surrogate primary key uniquely identifying each drop-ship sourcing row.
• HEADER_ID — foreign key to OE_ORDER_HEADERS_ALL, identifying the sales order header.
• LINE_ID — foreign key to OE_ORDER_LINES_ALL, identifying the specific sales order line being sourced.
• REQUISITION_HEADER_ID — foreign key to PO_REQUISITION_HEADERS_ALL, linking to the internal requisition header.
• REQUISITION_LINE_ID — foreign key to PO_REQUISITION_LINES_ALL, linking to the requisition line.
• PO_HEADER_ID — foreign key to PO_HEADERS_ALL, identifying the purchase order header created for the drop-ship fulfillment.
• PO_LINE_ID — foreign key to PO_LINES_ALL, identifying the purchase order line.
• LINE_LOCATION_ID — the purchase order shipment/line location reference associated with the sourcing relationship.
• PO_RELEASE_ID — foreign key to PO_RELEASES_ALL, used when the sourcing is against a blanket purchase agreement release.
• ORG_ID — the operating unit identifier, supporting multi-org access control and secure reporting.
• DESTINATION_ORGANIZATION_ID — the inventory organization designated as the receiving/ship-to destination for the supplied goods.

The business-key candidates are the combination of HEADER_ID and LINE_ID together with the procurement identifiers; the surrogate DROP_SHIP_SOURCE_ID provides the stable, single-column primary key referenced internally. Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and INST_ID are also maintained.

Common Use Cases and Queries

This table is most often queried to reconcile sales orders against their downstream purchasing commitments. Typical reporting includes identifying which sales order lines were drop-shipped, tracing the associated requisition and purchase order, monitoring the procurement status of back-to-back orders, and reconciling receipt discrepancies against the originating sales line.

A common pattern joins the sourcing row back to the sales order line and forward to the purchase order line:

• SELECT ola.line_number, ola.ordered_item, pda.segment1 AS po_number, pla.line_num FROM oe_drop_ship_sources ds, oe_order_lines_all ola, po_headers_all pha, po_lines_all pla, po_header_types_all pht WHERE ds.line_id = ola.line_id AND ds.po_header_id = pha.po_header_id AND ds.po_line_id = pla.po_line_id
• Filter by ORG_ID to restrict results to a single operating unit, and by DESTINATION_ORGANIZATION_ID to isolate activity for a specific receiving warehouse.
• Join to PO_REQUISITION_LINES_ALL via REQUISITION_LINE_ID to report requisition-to-order sourcing gaps before purchase order creation.
• Join to PO_RELEASES_ALL via PO_RELEASE_ID where blanket agreement releases are involved.

Reporting use cases include drop-ship lead-time analysis, supplier performance measurement tied to sales commitments, and margin analysis where cost flows from the purchase order line to the sales order line.

Related Objects

OE_DROP_SHIP_SOURCES sits at the center of a documented set of foreign key relationships:

• OE_ORDER_HEADERS_ALL — joined on HEADER_ID; parent sales order header.
• OE_ORDER_LINES_ALL — joined on LINE_ID; the sales order line being sourced.
• PO_HEADERS_ALL — joined on PO_HEADER_ID; the purchase order header.
• PO_LINES_ALL — joined on PO_LINE_ID; the purchase order line.
• PO_REQUISITION_HEADERS_ALL — joined on REQUISITION_HEADER_ID; the requisition header.
• PO_REQUISITION_LINES_ALL — joined on REQUISITION_LINE_ID; the requisition line.
• PO_RELEASES_ALL — joined on PO_RELEASE_ID; the blanket agreement release where applicable.

These relationships make OE_DROP_SHIP_SOURCES the definitive bridge between the Order Management and Purchasing schemas, and it is an essential object for any integration or reporting layer that spans sales order and procurement data in Oracle EBS 12.1.1 and 12.2.2.