Search Results op_ordr_dtl




Overview
Explore in AI Chat

Overview

The OP_ORDR_DTL table is a core transactional data store within the Oracle E-Business Suite (EBS) Process Manufacturing Logistics (GML) module. It serves as the detailed line-level repository for sales and distribution orders, capturing all granular information associated with individual order lines. Each record in this table represents a specific item or service requested on an order, linked to a parent header record in the OP_ORDR_HDR table. Its primary role is to manage the lifecycle of order lines, from entry and pricing through to fulfillment and invoicing, integrating deeply with inventory, pricing, shipping, and customer master data.

Key Information Stored

The table's structure is defined by a composite primary key (ORDER_ID, LINE_ID), which uniquely identifies every order line within the system. Key columns and the data they hold include the LINE_ID for sequence, ITEM_ID linking to the IC_ITEM_MST_B table for the ordered product, and various quantity fields (e.g., ORDER_QTY, SHIP_QTY). Critical business rule columns are also present, such as PRICEFF_ID for applied pricing, LINE_STATUS for workflow tracking, and SHIPCUST_ID for the shipping customer. Financial and logistical attributes are extensively covered, including columns for FOB_CODE, TERMS_CODE, SHIP_MTHD, and multiple currency fields (BASE_CURRENCY, BILLING_CURRENCY). The table also maintains references for quality control (QC_GRADE_WANTED), sales representatives (SLSREP_CODE), and textual instructions (TEXT_CODE).

Common Use Cases and Queries

This table is central to operational reporting and process execution. Common use cases include generating pick slips and packing lists by querying open lines for a warehouse, analyzing line-level profitability by joining with pricing and cost tables, and tracking order fulfillment status. Support teams frequently query it to investigate pricing discrepancies or shipment holds (HOLDREAS_CODE). A typical SQL pattern retrieves all lines for a specific order or customer, often joining with header and item master data:

  • SELECT ood.line_id, ood.item_id, iim.item_no, ood.order_qty, ood.ship_qty, ood.line_status FROM gml.op_ordr_dtl ood JOIN ic_item_mst iim ON ood.item_id = iim.item_id WHERE ood.order_id = :p_order_id ORDER BY ood.line_id;

Another common reporting query aggregates sales by item or sales representative over a date range, requiring a join to OP_ORDR_HDR for the order date.

Related Objects

OP_ORDR_DTL maintains extensive foreign key relationships, acting as a hub that connects order lines to master and transactional data across EBS. Its most critical relationship is with the parent header table, OP_ORDR_HDR, via ORDER_ID. Key documented relationships, as per the ETRM, include: