# Oracle EBS Order Management Tables Reference (OE, WSH and Order Import)

> Source: https://netray.co/tools/oracle-ebs-order-management-tables-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

The OE_ and WSH_ tables behind a sales order in EBS 12.1/12.2, with primary keys, join columns and the order import error tables.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS developer
- OM functional consultant
- Report writer
- ERP administrator

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### Table

**Key:** tbl

**Type:** text

#### Area

**Key:** area

**Type:** select

**Options:**

- Header
- Line
- Hold
- Pricing
- Shipping
- Interface
- Setup

#### What it holds

**Key:** holds

**Type:** text

#### Primary key

**Key:** pk

**Type:** text

#### Key join columns

**Key:** joins

**Type:** text

#### Notes

**Key:** notes

**Type:** text

**Rows:**

**Tbl:** OE_ORDER_HEADERS_ALL

**Area:** Header

**Holds:** One row per sales order or return header

**Pk:** HEADER_ID

**Joins:** SOLD_TO_ORG_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID; ORDER_TYPE_ID = OE_TRANSACTION_TYPES_ALL.TRANSACTION_TYPE_ID; ORDER_SOURCE_ID = OE_ORDER_SOURCES.ORDER_SOURCE_ID; SHIP_TO_ORG_ID and INVOICE_TO_ORG_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID

**Notes:** ORDER_NUMBER is what users see; FLOW_STATUS_CODE (ENTERED, BOOKED, CLOSED, CANCELLED); BOOKED_FLAG; ORG_ID is the operating unit

**Tbl:** OE_ORDER_LINES_ALL

**Area:** Line

**Holds:** Order lines, including split shipments and model/option lines

**Pk:** LINE_ID

**Joins:** HEADER_ID; INVENTORY_ITEM_ID + SHIP_FROM_ORG_ID = MTL_SYSTEM_ITEMS_B (INVENTORY_ITEM_ID, ORGANIZATION_ID); LINE_TYPE_ID = OE_TRANSACTION_TYPES_ALL.TRANSACTION_TYPE_ID

**Notes:** Displayed line number is LINE_NUMBER.SHIPMENT_NUMBER (plus OPTION_NUMBER for configurations); ORDERED_QUANTITY, SHIPPED_QUANTITY, FLOW_STATUS_CODE; TOP_MODEL_LINE_ID and LINK_TO_LINE_ID for models

**Tbl:** OE_ORDER_HEADERS

**Area:** Header

**Holds:** MOAC-secured access to OE_ORDER_HEADERS_ALL

**Pk:** HEADER_ID

**Joins:** Same columns as the _ALL table

**Notes:** In R12 an APPS synonym with a VPD policy that filters by the operating units in the MO security context (a view in 11i); returns no rows until MO_GLOBAL context is set

**Tbl:** OE_ORDER_LINES

**Area:** Line

**Holds:** MOAC-secured access to OE_ORDER_LINES_ALL

**Pk:** LINE_ID

**Joins:** Same columns as the _ALL table

**Notes:** Same behaviour as OE_ORDER_HEADERS; use the _ALL table plus an explicit ORG_ID filter in reports and background jobs

**Tbl:** OE_TRANSACTION_TYPES_ALL

**Area:** Setup

**Holds:** Order types and line types (workflow assignment, defaults, category)

**Pk:** TRANSACTION_TYPE_ID

**Joins:** = OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID or OE_ORDER_LINES_ALL.LINE_TYPE_ID

**Notes:** TRANSACTION_TYPE_CODE is ORDER or LINE; ORDER_CATEGORY_CODE (ORDER, RETURN, MIXED); the name is not here

**Tbl:** OE_TRANSACTION_TYPES_TL

**Area:** Setup

**Holds:** Translated name and description of each transaction type

**Pk:** TRANSACTION_TYPE_ID, LANGUAGE

**Joins:** TRANSACTION_TYPE_ID = OE_TRANSACTION_TYPES_ALL.TRANSACTION_TYPE_ID

**Notes:** Filter LANGUAGE = USERENV('LANG'); NAME is the order type users pick, for example 'Standard'

**Tbl:** OE_ORDER_SOURCES

**Area:** Setup

**Holds:** Order sources such as Online, Copy, Internal and import sources

**Pk:** ORDER_SOURCE_ID

**Joins:** = OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID and OE_HEADERS_IFACE_ALL.ORDER_SOURCE_ID

**Notes:** Not org-striped (no _ALL); ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF identifies an imported order

**Tbl:** OE_PRICE_ADJUSTMENTS

**Area:** Pricing

**Holds:** Discounts, surcharges, freight charges and other modifiers applied to header or line

**Pk:** PRICE_ADJUSTMENT_ID

**Joins:** HEADER_ID, LINE_ID (null for header-level); LIST_HEADER_ID, LIST_LINE_ID = QP_LIST_HEADERS_B / QP_LIST_LINES

**Notes:** LIST_LINE_TYPE_CODE (DIS, SUR, FREIGHT_CHARGE and others), OPERAND, ARITHMETIC_OPERATOR, ADJUSTED_AMOUNT, APPLIED_FLAG

**Tbl:** OE_ORDER_PRICE_ATTRIBS

**Area:** Pricing

**Holds:** Pricing and qualifier attributes captured on the order

**Pk:** ORDER_PRICE_ATTRIB_ID

**Joins:** HEADER_ID, LINE_ID

**Notes:** Holds PRICING_ATTRIBUTEn and context values sent to the pricing engine

**Tbl:** OE_SALES_CREDITS

**Area:** Header

**Holds:** Sales credit splits per order or line

**Pk:** SALES_CREDIT_ID

**Joins:** HEADER_ID, LINE_ID; SALESREP_ID = JTF_RS_SALESREPS.SALESREP_ID

**Notes:** PERCENT and SALES_CREDIT_TYPE_ID (quota or non-quota); flows to AR sales credits at invoicing

**Tbl:** OE_HOLD_DEFINITIONS

**Area:** Hold

**Holds:** Hold names and their type (credit check, GSA, configurator and user-defined)

**Pk:** HOLD_ID

**Joins:** HOLD_ID = OE_HOLD_SOURCES_ALL.HOLD_ID

**Notes:** NAME, TYPE_CODE; ITEM_TYPE and ACTIVITY_NAME when the hold applies only at a workflow activity

**Tbl:** OE_HOLD_SOURCES_ALL

**Area:** Hold

**Holds:** Why a hold was applied: the entity it targets (order, customer, item, site)

**Pk:** HOLD_SOURCE_ID

**Joins:** HOLD_ID = OE_HOLD_DEFINITIONS.HOLD_ID; HOLD_RELEASE_ID = OE_HOLD_RELEASES.HOLD_RELEASE_ID

**Notes:** HOLD_ENTITY_CODE and HOLD_ENTITY_ID describe the target; RELEASED_FLAG; HOLD_UNTIL_DATE

**Tbl:** OE_ORDER_HOLDS_ALL

**Area:** Hold

**Holds:** Holds actually sitting on an order or line

**Pk:** ORDER_HOLD_ID

**Joins:** HEADER_ID, LINE_ID (null = header-level hold); HOLD_SOURCE_ID = OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID

**Notes:** Active holds: RELEASED_FLAG = 'N'; join through the hold source to get the hold name

**Tbl:** OE_HOLD_RELEASES

**Area:** Hold

**Holds:** Release reason and comment for released holds

**Pk:** HOLD_RELEASE_ID

**Joins:** = OE_ORDER_HOLDS_ALL.HOLD_RELEASE_ID and OE_HOLD_SOURCES_ALL.HOLD_RELEASE_ID

**Notes:** RELEASE_REASON_CODE, RELEASE_COMMENT, CREATED_BY shows who released it

**Tbl:** OE_SETS

**Area:** Line

**Holds:** Ship sets, arrival sets and fulfillment sets

**Pk:** SET_ID

**Joins:** = OE_ORDER_LINES_ALL.SHIP_SET_ID or ARRIVAL_SET_ID; fulfillment set membership is in OE_LINE_SETS

**Notes:** SET_TYPE (SHIP_SET, ARRIVAL_SET, FULFILLMENT_SET), SET_NAME, HEADER_ID

**Tbl:** OE_ORDER_LINES_HISTORY

**Area:** Line

**Holds:** Prior versions of line columns captured by audit trail and cancellations

**Pk:** No single surrogate key; query by LINE_ID

**Joins:** LINE_ID, HEADER_ID

**Notes:** HIST_TYPE_CODE, HIST_CREATION_DATE, REASON_CODE; populated only when the audit trail system parameter is enabled or quantities are cancelled

**Tbl:** OE_SYSTEM_PARAMETERS_ALL

**Area:** Setup

**Holds:** Per operating unit OM parameters, notably the item validation organization

**Pk:** ORG_ID

**Joins:** MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID

**Notes:** Read MASTER_ORGANIZATION_ID here; most other R12 system parameters are stored as code/value rows in OE_SYS_PARAMETERS_ALL

**Tbl:** OE_HEADERS_IFACE_ALL

**Area:** Interface

**Holds:** Order Import header rows (create, update, delete)

**Pk:** ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF (+ ORG_ID)

**Joins:** Child interface rows by ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF

**Notes:** OPERATION_CODE (INSERT, UPDATE, DELETE), ERROR_FLAG, REQUEST_ID; rows stay here when import fails

**Tbl:** OE_LINES_IFACE_ALL

**Area:** Interface

**Holds:** Order Import line rows

**Pk:** ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF + ORIG_SYS_LINE_REF (+ ORIG_SYS_SHIPMENT_REF)

**Joins:** Parent header by ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF

**Notes:** INVENTORY_ITEM_ID or item identifiers, ORDERED_QUANTITY, ERROR_FLAG

**Tbl:** OE_PRICE_ADJS_IFACE_ALL

**Area:** Interface

**Holds:** Price adjustments to import with the order

**Pk:** ORIG_SYS_DOCUMENT_REF + ORIG_SYS_LINE_REF + ORIG_SYS_DISCOUNT_REF

**Joins:** Header and line interface rows by the ORIG_SYS references

**Notes:** Used when the source system has already priced the order

**Tbl:** OE_ACTIONS_IFACE_ALL

**Area:** Interface

**Holds:** Actions to run after import, such as booking or applying a hold

**Pk:** ORIG_SYS_DOCUMENT_REF (+ ORIG_SYS_LINE_REF) + OPERATION_CODE

**Joins:** Header or line interface rows by the ORIG_SYS references

**Notes:** Common OPERATION_CODE value is BOOK_ORDER; alternatively set BOOKED_FLAG on the header interface

**Tbl:** OE_PROCESSING_MSGS

**Area:** Interface

**Holds:** Messages raised by Order Import and the process order API

**Pk:** TRANSACTION_ID

**Joins:** ORIGINAL_SYS_DOCUMENT_REF + ORDER_SOURCE_ID to the interface rows; HEADER_ID and LINE_ID for existing orders; REQUEST_ID

**Notes:** Note the columns are ORIGINAL_SYS_DOCUMENT_REF and ORIGINAL_SYS_DOCUMENT_LINE_REF here, not ORIG_SYS_DOCUMENT_REF; ENTITY_CODE tells you header, line or adjustment

**Tbl:** OE_PROCESSING_MSGS_TL

**Area:** Interface

**Holds:** Translated message text for OE_PROCESSING_MSGS

**Pk:** TRANSACTION_ID, LANGUAGE

**Joins:** TRANSACTION_ID = OE_PROCESSING_MSGS.TRANSACTION_ID

**Notes:** MESSAGE_TEXT is the error you need; filter LANGUAGE = USERENV('LANG')

**Tbl:** WSH_DELIVERY_DETAILS

**Area:** Shipping

**Holds:** One row per shippable line (and per split); also containers (LPNs)

**Pk:** DELIVERY_DETAIL_ID

**Joins:** SOURCE_CODE = 'OE', SOURCE_HEADER_ID = HEADER_ID, SOURCE_LINE_ID = LINE_ID; MOVE_ORDER_LINE_ID = MTL_TXN_REQUEST_LINES.LINE_ID

**Notes:** RELEASED_STATUS: N not ready, R ready to release, S released to warehouse, Y staged, C shipped, B backordered, D cancelled, X not applicable; OE_INTERFACED_FLAG, INV_INTERFACED_FLAG

**Tbl:** WSH_DELIVERY_ASSIGNMENTS

**Area:** Shipping

**Holds:** Assignment of delivery details to deliveries and to parent containers

**Pk:** DELIVERY_ASSIGNMENT_ID

**Joins:** DELIVERY_DETAIL_ID = WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID; DELIVERY_ID = WSH_NEW_DELIVERIES.DELIVERY_ID; PARENT_DELIVERY_DETAIL_ID

**Notes:** DELIVERY_ID is null until the detail is assigned to a delivery; outer join it

**Tbl:** WSH_NEW_DELIVERIES

**Area:** Shipping

**Holds:** Deliveries (the shipment users confirm in Shipping Transactions)

**Pk:** DELIVERY_ID

**Joins:** DELIVERY_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID and WSH_DELIVERY_LEGS.DELIVERY_ID

**Notes:** NAME is the delivery number shown to users; STATUS_CODE; WAYBILL; ORGANIZATION_ID

**Tbl:** WSH_DELIVERY_LEGS

**Area:** Shipping

**Holds:** Links a delivery to its pick-up and drop-off trip stops

**Pk:** DELIVERY_LEG_ID

**Joins:** DELIVERY_ID; PICK_UP_STOP_ID and DROP_OFF_STOP_ID = WSH_TRIP_STOPS.STOP_ID

**Notes:** Needed to get from a delivery to its trip; bill of lading data hangs off the leg

**Tbl:** WSH_TRIP_STOPS

**Area:** Shipping

**Holds:** Stops on a trip (pick-up and drop-off locations)

**Pk:** STOP_ID

**Joins:** TRIP_ID = WSH_TRIPS.TRIP_ID; STOP_LOCATION_ID = WSH_LOCATIONS.WSH_LOCATION_ID

**Notes:** STATUS_CODE; the pick-up stop closing triggers the ship confirm interfaces

**Tbl:** WSH_TRIPS

**Area:** Shipping

**Holds:** Trips (vehicle movements) that carry deliveries

**Pk:** TRIP_ID

**Joins:** TRIP_ID = WSH_TRIP_STOPS.TRIP_ID

**Notes:** NAME, STATUS_CODE, CARRIER_ID, SHIP_METHOD_CODE

**Tbl:** MTL_SALES_ORDERS

**Area:** Line

**Holds:** Inventory's key for a sales order, used as the demand source

**Pk:** SALES_ORDER_ID

**Joins:** SEGMENT1 = order number, SEGMENT2 = order type name, SEGMENT3 = order source name

**Notes:** MTL_RESERVATIONS and MTL_MATERIAL_TRANSACTIONS reference SALES_ORDER_ID, not OE HEADER_ID

**Tbl:** MTL_RESERVATIONS

**Area:** Line

**Holds:** Reservations of on-hand or supply against order lines

**Pk:** RESERVATION_ID

**Joins:** DEMAND_SOURCE_LINE_ID = OE_ORDER_LINES_ALL.LINE_ID; DEMAND_SOURCE_HEADER_ID = MTL_SALES_ORDERS.SALES_ORDER_ID

**Notes:** DEMAND_SOURCE_TYPE_ID 2 = sales order, 8 = internal order; RESERVATION_QUANTITY, SUBINVENTORY_CODE, LOT_NUMBER

**Tbl:** RA_INTERFACE_LINES_ALL

**Area:** Interface

**Holds:** Invoice lines OM sends to AutoInvoice

**Pk:** INTERFACE_LINE_ID

**Joins:** INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'; INTERFACE_LINE_ATTRIBUTE1 = order number; INTERFACE_LINE_ATTRIBUTE6 = LINE_ID

**Notes:** Populated by the Invoice Interface workflow activity; INTERFACE_LINE_ATTRIBUTE2 holds the order type name; SALES_ORDER column also carries the order number

**Tbl:** RA_CUSTOMER_TRX_LINES_ALL

**Area:** Interface

**Holds:** AR invoice lines created by AutoInvoice from order lines

**Pk:** CUSTOMER_TRX_LINE_ID

**Joins:** INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' and INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OE_ORDER_LINES_ALL.LINE_ID)

**Notes:** The reliable way to go from an order line to its invoice; the attributes are VARCHAR2 so convert the LINE_ID

**Notes:** Scope: Oracle EBS 12.1 and 12.2 Order Management (ONT) and Shipping Execution (WSH). Check exact columns for your patch level in eTRM before building production code.

## Content

**Intro:** A searchable map of the OE_ and WSH_ tables behind an Oracle EBS 12.1/12.2 sales order, from header and line through holds, pricing, delivery and the AR interface. Each row gives the primary key and the join columns you actually use in SQL.

**Sections:**

#### Where these tables appear in the Sales Orders form

**Body:** The Sales Orders form (OEXOEORD) reads OE_ORDER_HEADERS_ALL on the Order Information tab and OE_ORDER_LINES_ALL on Line Items. Use Help > Diagnostics > Examine on a field to see the block and the underlying column, for example HEADER_ID or LINE_ID. Actions > Additional Order Information > Holds shows OE_ORDER_HOLDS_ALL joined through OE_HOLD_SOURCES_ALL to OE_HOLD_DEFINITIONS. Actions > View Adjustments reads OE_PRICE_ADJUSTMENTS. The Shipping Transactions form works on WSH_DELIVERY_DETAILS (Lines/LPNs tab) and WSH_NEW_DELIVERIES (Deliveries tab). The order type you pick is the NAME in OE_TRANSACTION_TYPES_TL, which is why queries that need the type name must join the _TL table on TRANSACTION_TYPE_ID and LANGUAGE.

**Bullets:**

- Order number: OE_ORDER_HEADERS_ALL.ORDER_NUMBER (unique within order type numbering, so also filter ORG_ID)
- Line 1.2 on screen: LINE_NUMBER = 1, SHIPMENT_NUMBER = 2
- Order status on screen: FLOW_STATUS_CODE, decoded through lookup type FLOW_STATUS

#### MOAC: _ALL tables versus secured synonyms

**Body:** In R12, OE_ORDER_HEADERS and OE_ORDER_LINES are APPS synonyms over the _ALL tables with a Virtual Private Database policy that filters rows to the operating units in the current Multi-Org Access Control context. In SQL Developer or a database job with no context set they return nothing, which is the most common 'the order is missing' complaint. Either call MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id) after FND_GLOBAL.APPS_INITIALIZE, or query the _ALL tables and add ORG_ID to the WHERE clause. Reports and extracts should normally use _ALL with an explicit org filter so results do not depend on session state. Setup tables without an org stripe, such as OE_ORDER_SOURCES and OE_HOLD_DEFINITIONS, need no context.

#### Worked join: header to line to delivery

**Body:** To see where each line of order 100245 sits in shipping: SELECT h.order_number, l.line_number || '.' || l.shipment_number AS line_no, l.flow_status_code, wdd.delivery_detail_id, wdd.released_status, wnd.name AS delivery FROM oe_order_headers_all h JOIN oe_order_lines_all l ON l.header_id = h.header_id LEFT JOIN wsh_delivery_details wdd ON wdd.source_line_id = l.line_id AND wdd.source_code = 'OE' LEFT JOIN wsh_delivery_assignments wda ON wda.delivery_detail_id = wdd.delivery_detail_id LEFT JOIN wsh_new_deliveries wnd ON wnd.delivery_id = wda.delivery_id WHERE h.order_number = 100245 AND h.org_id = 204. Use outer joins: lines that are not yet booked have no delivery detail, and details that are not assigned have a null DELIVERY_ID. A line split at ship confirm shows as two delivery details for one LINE_ID, or two order lines once OM splits it.

#### Checking Order Import errors

**Body:** Order Import (concurrent program Order Import, short name OEOIMP) leaves failed rows in OE_HEADERS_IFACE_ALL and OE_LINES_IFACE_ALL with ERROR_FLAG = 'Y'. The reason is in OE_PROCESSING_MSGS joined to OE_PROCESSING_MSGS_TL: SELECT m.original_sys_document_ref, m.original_sys_document_line_ref, t.message_text FROM oe_processing_msgs m JOIN oe_processing_msgs_tl t ON t.transaction_id = m.transaction_id AND t.language = USERENV('LANG') WHERE m.order_source_id = :src AND m.original_sys_document_ref = :ref. The Order Import Corrections form (Orders, Returns > Import Orders > Corrections) shows the same messages and lets users fix and revalidate. After a fix, reset ERROR_FLAG and REQUEST_ID to null on the interface rows, or resubmit from the corrections form, before running import again.

**Cta:** If you want planners and customer service to ask Oracle EBS about orders, holds and deliveries in plain English on your own servers, we can set that up read-only over these tables.

**Faq:**

#### What is the sales order table in Oracle EBS?

**Answer:** Sales order headers are in OE_ORDER_HEADERS_ALL (key HEADER_ID, user-facing ORDER_NUMBER) and lines in OE_ORDER_LINES_ALL (key LINE_ID, joined by HEADER_ID). Shipping detail for each line is in WSH_DELIVERY_DETAILS, linked by SOURCE_LINE_ID with SOURCE_CODE = 'OE'. Use the _ALL tables with an ORG_ID filter in reports.

#### Which table holds the order source in Oracle Apps?

**Answer:** OE_ORDER_SOURCES, keyed by ORDER_SOURCE_ID, with NAME holding values such as Online or Copy plus any import sources you defined. It is not org-striped. OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID points to it, and ORDER_SOURCE_ID plus ORIG_SYS_DOCUMENT_REF identifies an order that came in through Order Import.

#### Where is the transaction type name stored?

**Answer:** OE_TRANSACTION_TYPES_ALL holds the setup (TRANSACTION_TYPE_CODE ORDER or LINE, ORDER_CATEGORY_CODE) and OE_TRANSACTION_TYPES_TL holds NAME and DESCRIPTION per language. Join TRANSACTION_TYPE_ID to OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID for the order type or to OE_ORDER_LINES_ALL.LINE_TYPE_ID for the line type, and filter LANGUAGE = USERENV('LANG').

#### Which table shows Order Import errors?

**Answer:** OE_PROCESSING_MSGS with its text in OE_PROCESSING_MSGS_TL, joined on TRANSACTION_ID. Match ORDER_SOURCE_ID and ORIGINAL_SYS_DOCUMENT_REF to the interface rows. Failed rows stay in OE_HEADERS_IFACE_ALL and OE_LINES_IFACE_ALL with ERROR_FLAG = 'Y', and the Order Import Corrections form shows the same messages.

## Search terms

oracle ebs om tables, oracle r12 order management tables, sales order table in oracle ebs, order source table in oracle apps, oracle ebs order import error table, oracle ebs transaction type table, oe_order_lines_all wsh_delivery_details join, oracle ebs released_status values

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json