Results for “alloc_ind”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OP_ORDR_DTL is the order line detail table within the Oracle E-Business Suite Process Manufacturing Logistics (GML) module, the schema more commonly known as Oracle Process Manufacturing (OPM) Order Management. Each row in the table represents a single line on a sales order header, holding the ordered item, quantities, pricing, shipping, allocation, and status attributes that drive fulfillment, invoicing, and shipment processing in a process manufacturing environment. The table is owned by the GML schema and is documented as VALID in both EBS 12.1.1 and 12.2.2 (ETRM reference). In the documented physical schema for 12.2.2, OP_ORDR_DTL contains 145 columns.

From a Data Vault modeling perspective, the ETRM heuristic classification places OP_ORDR_DTL as satellite-leaning. This reflects the fact that the table primarily stores descriptive and transactional attributes that describe order lines, with its key relationships serving more as reference lookups than as true many-to-many link structures. Analysts designing a warehouse layer around OPM data should treat OP_ORDR_DTL as the central detail satellite attached to the OP_ORDR_HDR hub via ORDER_ID.

Key Information Stored

The primary key of the table is the surrogate key LINE_ID, defined by the index OP_ORDR_DTL_PK. A second unique index, OP_ORDR_DTL_UK, is defined on the combination (ORDER_ID, LINE_ID), establishing the business-key candidate that ties each line back to its parent order. The most significant columns include:

• ORDER_ID – Foreign key to OP_ORDR_HDR; identifies the parent sales order.
• LINE_NO – The user-visible line number within the order.
• ITEM_ID – The inventory item ordered, referencing IC_ITEM_MST / IC_ITEM_MST_B.
• ORDER_QTY1, ORDER_QTY2, ORDER_UM1, ORDER_UM2 – Ordered quantities and their units of measure.
• SHIP_QTY1, SHIP_QTY2, SHIP_VOLUME – Shipped quantities and volume captured at fulfillment.
• BASE_PRICE, NET_PRICE, EXTENDED_PRICE, PRICE_UM, PRICE_ID – Pricing attributes, including the pricing unit and the price-list detail reference.
• LINE_STATUS – Current line status, referencing OP_ORDR_STS_B.
• SHIP_STATUS, ALLOC_IND, ALLOC_QTY, INVENTORY_QTY – Allocation and inventory availability indicators.
• REQUESTED_SHIPDATE, PROMISED_SHIPDATE, SCHED_SHIPDATE, ACTUAL_SHIPDATE, REQUIRED_DLVDATE, ACTUAL_DLVDATE – The scheduling and delivery date set.
• TO_WHSE, FROM_WHSE – Warehouse references for shipping and sourcing.
• SLSREP_CODE, COMMISSION_CODE – Sales representative and commission linkage.
• GL_POSTED_IND, COMPLETED_IND, DELETE_MARK – Processing and lifecycle flags.

Common Use Cases and Queries

The table supports order-line reporting, backlog analysis, shipment tracking, and pricing audits. A typical query joins OP_ORDR_DTL to OP_ORDR_HDR and OP_ORDR_STS_B to produce an open-order backlog report:

SELECT h.ORDER_NO, d.LINE_NO, d.ITEM_ID, d.ORDER_QTY1,
       d.SHIP_QTY1, d.LINE_STATUS, d.PROMISED_SHIPDATE
FROM   GML.OP_ORDR_DTL d, GML.OP_ORDR_HDR h, GML.OP_ORDR_STS_B s
WHERE  d.ORDER_ID = h.ORDER_ID
AND    d.LINE_STATUS = s.LINE_STATUS
AND    d.DELETE_MARK = 0;

Other frequent patterns include reconciling shipped versus ordered quantities (ORDER_QTY1 vs SHIP_QTY1), analyzing allocation status for distribution planning, and auditing price overrides via PRICEREAS_CODE. Because LINE_ID is referenced by OP_AUDT_DTL, OP_ORDR_CHG, and CR_DOPR_TBL, reports on line-level history and changes are also common.

Related Objects

The most significant related objects, based on documented foreign key relationships, are:

• OP_ORDR_HDR – Parent order header, joined on ORDER_ID; the primary hub for the line satellite.
• IC_ITEM_MST / IC_ITEM_MST_B – Item master, joined on ITEM_ID.
• OP_ORDR_STS_B – Line status codes, joined on LINE_STATUS.
• OP_CUST_MST – Customer master, referenced four times (SHIPCUST_ID, CONSIGNEE_ID, ULTIMATE_SHIPCUST_ID, SOLDTOCUST_ID).
• IC_WHSE_MST – Warehouse master, referenced via TO_WHSE and FROM_WHSE.
• OP_PRCE_EFF / OP_PRCE_CDS – Pricing effectiveness and reason codes.
• OP_AUDT_DTL – Audit detail table referencing LINE_ID.
• OP_ORDR_CHG – Order change log referencing LINE_ID.
• OP_PRSL_DTL – Presales order line, referenced via PRESALES_ORDLINE_ID.
• OP_CNTR_DTL – Contract detail, referenced via PRICE_ID.