OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

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

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.

Download:

Table	Area	What it holds	Primary key	Key join columns	Notes
OE_ORDER_HEADERS_ALL	Header	One row per sales order or return header	HEADER_ID	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	ORDER_NUMBER is what users see; FLOW_STATUS_CODE (ENTERED, BOOKED, CLOSED, CANCELLED); BOOKED_FLAG; ORG_ID is the operating unit
OE_ORDER_LINES_ALL	Line	Order lines, including split shipments and model/option lines	LINE_ID	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	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
OE_ORDER_HEADERS	Header	MOAC-secured access to OE_ORDER_HEADERS_ALL	HEADER_ID	Same columns as the _ALL table	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
OE_ORDER_LINES	Line	MOAC-secured access to OE_ORDER_LINES_ALL	LINE_ID	Same columns as the _ALL table	Same behaviour as OE_ORDER_HEADERS; use the _ALL table plus an explicit ORG_ID filter in reports and background jobs
OE_TRANSACTION_TYPES_ALL	Setup	Order types and line types (workflow assignment, defaults, category)	TRANSACTION_TYPE_ID	= OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID or OE_ORDER_LINES_ALL.LINE_TYPE_ID	TRANSACTION_TYPE_CODE is ORDER or LINE; ORDER_CATEGORY_CODE (ORDER, RETURN, MIXED); the name is not here
OE_TRANSACTION_TYPES_TL	Setup	Translated name and description of each transaction type	TRANSACTION_TYPE_ID, LANGUAGE	TRANSACTION_TYPE_ID = OE_TRANSACTION_TYPES_ALL.TRANSACTION_TYPE_ID	Filter LANGUAGE = USERENV('LANG'); NAME is the order type users pick, for example 'Standard'
OE_ORDER_SOURCES	Setup	Order sources such as Online, Copy, Internal and import sources	ORDER_SOURCE_ID	= OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID and OE_HEADERS_IFACE_ALL.ORDER_SOURCE_ID	Not org-striped (no _ALL); ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF identifies an imported order
OE_PRICE_ADJUSTMENTS	Pricing	Discounts, surcharges, freight charges and other modifiers applied to header or line	PRICE_ADJUSTMENT_ID	HEADER_ID, LINE_ID (null for header-level); LIST_HEADER_ID, LIST_LINE_ID = QP_LIST_HEADERS_B / QP_LIST_LINES	LIST_LINE_TYPE_CODE (DIS, SUR, FREIGHT_CHARGE and others), OPERAND, ARITHMETIC_OPERATOR, ADJUSTED_AMOUNT, APPLIED_FLAG
OE_ORDER_PRICE_ATTRIBS	Pricing	Pricing and qualifier attributes captured on the order	ORDER_PRICE_ATTRIB_ID	HEADER_ID, LINE_ID	Holds PRICING_ATTRIBUTEn and context values sent to the pricing engine
OE_SALES_CREDITS	Header	Sales credit splits per order or line	SALES_CREDIT_ID	HEADER_ID, LINE_ID; SALESREP_ID = JTF_RS_SALESREPS.SALESREP_ID	PERCENT and SALES_CREDIT_TYPE_ID (quota or non-quota); flows to AR sales credits at invoicing
OE_HOLD_DEFINITIONS	Hold	Hold names and their type (credit check, GSA, configurator and user-defined)	HOLD_ID	HOLD_ID = OE_HOLD_SOURCES_ALL.HOLD_ID	NAME, TYPE_CODE; ITEM_TYPE and ACTIVITY_NAME when the hold applies only at a workflow activity
OE_HOLD_SOURCES_ALL	Hold	Why a hold was applied: the entity it targets (order, customer, item, site)	HOLD_SOURCE_ID	HOLD_ID = OE_HOLD_DEFINITIONS.HOLD_ID; HOLD_RELEASE_ID = OE_HOLD_RELEASES.HOLD_RELEASE_ID	HOLD_ENTITY_CODE and HOLD_ENTITY_ID describe the target; RELEASED_FLAG; HOLD_UNTIL_DATE
OE_ORDER_HOLDS_ALL	Hold	Holds actually sitting on an order or line	ORDER_HOLD_ID	HEADER_ID, LINE_ID (null = header-level hold); HOLD_SOURCE_ID = OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID	Active holds: RELEASED_FLAG = 'N'; join through the hold source to get the hold name
OE_HOLD_RELEASES	Hold	Release reason and comment for released holds	HOLD_RELEASE_ID	= OE_ORDER_HOLDS_ALL.HOLD_RELEASE_ID and OE_HOLD_SOURCES_ALL.HOLD_RELEASE_ID	RELEASE_REASON_CODE, RELEASE_COMMENT, CREATED_BY shows who released it
OE_SETS	Line	Ship sets, arrival sets and fulfillment sets	SET_ID	= OE_ORDER_LINES_ALL.SHIP_SET_ID or ARRIVAL_SET_ID; fulfillment set membership is in OE_LINE_SETS	SET_TYPE (SHIP_SET, ARRIVAL_SET, FULFILLMENT_SET), SET_NAME, HEADER_ID
OE_ORDER_LINES_HISTORY	Line	Prior versions of line columns captured by audit trail and cancellations	No single surrogate key; query by LINE_ID	LINE_ID, HEADER_ID	HIST_TYPE_CODE, HIST_CREATION_DATE, REASON_CODE; populated only when the audit trail system parameter is enabled or quantities are cancelled
OE_SYSTEM_PARAMETERS_ALL	Setup	Per operating unit OM parameters, notably the item validation organization	ORG_ID	MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID	Read MASTER_ORGANIZATION_ID here; most other R12 system parameters are stored as code/value rows in OE_SYS_PARAMETERS_ALL
OE_HEADERS_IFACE_ALL	Interface	Order Import header rows (create, update, delete)	ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF (+ ORG_ID)	Child interface rows by ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF	OPERATION_CODE (INSERT, UPDATE, DELETE), ERROR_FLAG, REQUEST_ID; rows stay here when import fails
OE_LINES_IFACE_ALL	Interface	Order Import line rows	ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF + ORIG_SYS_LINE_REF (+ ORIG_SYS_SHIPMENT_REF)	Parent header by ORDER_SOURCE_ID + ORIG_SYS_DOCUMENT_REF	INVENTORY_ITEM_ID or item identifiers, ORDERED_QUANTITY, ERROR_FLAG
OE_PRICE_ADJS_IFACE_ALL	Interface	Price adjustments to import with the order	ORIG_SYS_DOCUMENT_REF + ORIG_SYS_LINE_REF + ORIG_SYS_DISCOUNT_REF	Header and line interface rows by the ORIG_SYS references	Used when the source system has already priced the order
OE_ACTIONS_IFACE_ALL	Interface	Actions to run after import, such as booking or applying a hold	ORIG_SYS_DOCUMENT_REF (+ ORIG_SYS_LINE_REF) + OPERATION_CODE	Header or line interface rows by the ORIG_SYS references	Common OPERATION_CODE value is BOOK_ORDER; alternatively set BOOKED_FLAG on the header interface
OE_PROCESSING_MSGS	Interface	Messages raised by Order Import and the process order API	TRANSACTION_ID	ORIGINAL_SYS_DOCUMENT_REF + ORDER_SOURCE_ID to the interface rows; HEADER_ID and LINE_ID for existing orders; REQUEST_ID	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
OE_PROCESSING_MSGS_TL	Interface	Translated message text for OE_PROCESSING_MSGS	TRANSACTION_ID, LANGUAGE	TRANSACTION_ID = OE_PROCESSING_MSGS.TRANSACTION_ID	MESSAGE_TEXT is the error you need; filter LANGUAGE = USERENV('LANG')
WSH_DELIVERY_DETAILS	Shipping	One row per shippable line (and per split); also containers (LPNs)	DELIVERY_DETAIL_ID	SOURCE_CODE = 'OE', SOURCE_HEADER_ID = HEADER_ID, SOURCE_LINE_ID = LINE_ID; MOVE_ORDER_LINE_ID = MTL_TXN_REQUEST_LINES.LINE_ID	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
WSH_DELIVERY_ASSIGNMENTS	Shipping	Assignment of delivery details to deliveries and to parent containers	DELIVERY_ASSIGNMENT_ID	DELIVERY_DETAIL_ID = WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID; DELIVERY_ID = WSH_NEW_DELIVERIES.DELIVERY_ID; PARENT_DELIVERY_DETAIL_ID	DELIVERY_ID is null until the detail is assigned to a delivery; outer join it
WSH_NEW_DELIVERIES	Shipping	Deliveries (the shipment users confirm in Shipping Transactions)	DELIVERY_ID	DELIVERY_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID and WSH_DELIVERY_LEGS.DELIVERY_ID	NAME is the delivery number shown to users; STATUS_CODE; WAYBILL; ORGANIZATION_ID
WSH_DELIVERY_LEGS	Shipping	Links a delivery to its pick-up and drop-off trip stops	DELIVERY_LEG_ID	DELIVERY_ID; PICK_UP_STOP_ID and DROP_OFF_STOP_ID = WSH_TRIP_STOPS.STOP_ID	Needed to get from a delivery to its trip; bill of lading data hangs off the leg
WSH_TRIP_STOPS	Shipping	Stops on a trip (pick-up and drop-off locations)	STOP_ID	TRIP_ID = WSH_TRIPS.TRIP_ID; STOP_LOCATION_ID = WSH_LOCATIONS.WSH_LOCATION_ID	STATUS_CODE; the pick-up stop closing triggers the ship confirm interfaces
WSH_TRIPS	Shipping	Trips (vehicle movements) that carry deliveries	TRIP_ID	TRIP_ID = WSH_TRIP_STOPS.TRIP_ID	NAME, STATUS_CODE, CARRIER_ID, SHIP_METHOD_CODE
MTL_SALES_ORDERS	Line	Inventory's key for a sales order, used as the demand source	SALES_ORDER_ID	SEGMENT1 = order number, SEGMENT2 = order type name, SEGMENT3 = order source name	MTL_RESERVATIONS and MTL_MATERIAL_TRANSACTIONS reference SALES_ORDER_ID, not OE HEADER_ID
MTL_RESERVATIONS	Line	Reservations of on-hand or supply against order lines	RESERVATION_ID	DEMAND_SOURCE_LINE_ID = OE_ORDER_LINES_ALL.LINE_ID; DEMAND_SOURCE_HEADER_ID = MTL_SALES_ORDERS.SALES_ORDER_ID	DEMAND_SOURCE_TYPE_ID 2 = sales order, 8 = internal order; RESERVATION_QUANTITY, SUBINVENTORY_CODE, LOT_NUMBER
RA_INTERFACE_LINES_ALL	Interface	Invoice lines OM sends to AutoInvoice	INTERFACE_LINE_ID	INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'; INTERFACE_LINE_ATTRIBUTE1 = order number; INTERFACE_LINE_ATTRIBUTE6 = LINE_ID	Populated by the Invoice Interface workflow activity; INTERFACE_LINE_ATTRIBUTE2 holds the order type name; SALES_ORDER column also carries the order number
RA_CUSTOMER_TRX_LINES_ALL	Interface	AR invoice lines created by AutoInvoice from order lines	CUSTOMER_TRX_LINE_ID	INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' and INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OE_ORDER_LINES_ALL.LINE_ID)	The reliable way to go from an order line to its invoice; the attributes are VARCHAR2 so convert the LINE_ID

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.

Need an order-to-cash SQL pack for your instance?

Send your EBS version and the questions your team asks about orders, holds and shipments. We will return reviewed SQL against these tables for your release.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

Where these tables appear in the Sales Orders form

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.

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

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

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

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.

Frequently Asked Questions

What is the sales order table in Oracle EBS?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools