Search Results oe_drop_ship_links_v




Overview
Explore in AI Chat

Overview

OE_DROP_SHIP_LINKS_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, residing within the Order Management (ONT) product family. It is a validation status of VALID in both Oracle EBS 12.1.1 and 12.2.2. The view presents the association between sales order lines and their corresponding drop-ship sourcing documents, whether those documents are purchase orders or purchase requisitions. Its primary purpose in Oracle EBS is to populate the Dropship tab found in the additional line information region of the Sales Orders form. Beyond that form-driven role, it functions as a reporting and integration surface, allowing query tools, concurrent programs, and custom extensions to retrieve drop-ship linkage details for a given order line without navigating the multiple base tables directly.

The view is built as a UNION of two source blocks. The first block resolves drop-ship sourcing against purchase orders, and the second resolves against purchase requisitions. Because of this UNION, every qualifying line appears exactly once, with the TYPE column distinguishing procurement documents originating as purchase orders (value 1) from those originating as requisitions (value 2). The filter condition OEDSS.PO_RELEASE_ID IS NULL restricts the first block to header-and-line level purchase order links, excluding release-level references.

Underlying Base Objects

The documented base objects span Order Management, Purchasing, and Human Resources. OE_ORDER_LINES_ALL supplies the sales order line identity; OE_DROP_SHIP_SOURCES is the central linking table that maps an order line to a purchasing document, its line, and its shipment; PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, and PO_RELEASES_ALL provide purchase order header, line, shipment, and release data; PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL supply the requisition-side equivalent. Supplier information is drawn from PO_VENDORS, while ship-to addresses come from the HR_LOCATIONS view.

Buyer identification is resolved through PER_PEOPLE_F, an HR date-effective view of person records. The view restricts the join by requiring SYSDATE to fall between the person record's effective dates and by requiring PERP.EMPLOYEE_NUMBER to be non-null, ensuring only current employees with a valid employee number are returned as buyers. Document type labeling is provided by OE_LOOKUPS filtered on the 'DOCUMENT TYPE' lookup type and the 'PURCHASE ORDER' code. Additional referenced objects include FINANCIALS_SYSTEM_PARAMS_ALL, GL_SETS_OF_BOOKS, HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY, which serve organizational and security-related functions within the definition.

Key Columns

Common Use Cases and Queries

Typical scenarios include auditing drop-ship coverage per sales order line, reconciling ordered quantities against received quantities, and identifying lines still awaiting buyer action. Because BUYER_NAME is exposed directly, buyer-oriented reporting requires no join to PER_PEOPLE_F.

  • Listing all drop-ship links for a specific order line by filtering on LINE_ID.
  • Grouping open drop-ship quantities by buyer using BUYER_NAME and ORDERED_UOM.
  • Comparing QUANTITY against QUANTITY_RECEIVED to flag receipts pending against a linked document.
SELECT line_id, document_type, po_req_number, buyer_name,
       vendor_name, quantity, quantity_received, schedule_date
FROM   apps.oe_drop_ship_links_v
WHERE  line_id = :p_line_id;

SELECT buyer_name, COUNT(*) drop_ship_lines,
       SUM(quantity) ordered_qty
FROM   apps.oe_drop_ship_links_v
GROUP  BY buyer_name
ORDER  BY buyer_name;