Results for “oe_order_header_history”

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

Overview

OE_ORDER_HEADER_HISTORY is a table owned by the ONT schema in Oracle E-Business Suite Order Management. It stores the history of attribute changes for order headers, providing a versioned audit trail of the columns defined on OE_ORDER_HEADERS_ALL. Each row captures the state of an order header as it existed at a particular point in time, identified by a header reference and a version number, together with change metadata describing what was modified, by whom, and under which responsibility.

The table is populated by Order Management processing logic rather than by end-user entry, and is referenced primarily for auditing, change tracking, and historical reporting. It is functionally distinct from OE_ORDER_HEADERS_ALL, which holds only the current or active version of the order header.

From a heuristic Data Vault modeling perspective, this table is best classified as a link, based on its foreign key structure. It resolves relationships between the order header entity and the various customer, contact, site, and reference entities referenced by its customer-facing and order-type columns, while also carrying descriptive change attributes.

Key Information Stored

The table contains 183 documented columns. The most significant include:

• HEADER_ID — the identifier of the order header whose history is being recorded; the primary linkage back to the order header entity.
• VERSION_NUMBER — the version sequence for the header state, distinguishing multiple historical snapshots of the same header.
• CHANGE_SEQUENCE — a sequencing value used to order changes within the history.
• ORDER_NUMBER and ORDER_TYPE_ID — the business order number and the order type, where ORDER_TYPE_ID references SO_ORDER_TYPES_115_ALL.
• ORG_ID — the operating unit under which the order was placed.
• ORDERED_DATE, REQUEST_DATE, and BOOKED_DATE — key order lifecycle dates captured in the historical snapshot.
• FLOW_STATUS_CODE, OPEN_FLAG, BOOKED_FLAG, and CANCELLED_FLAG — status and lifecycle indicators as of the version.
• SOLD_TO_ORG_ID, SHIP_TO_ORG_ID, INVOICE_TO_ORG_ID, and DELIVER_TO_ORG_ID — the customer party/site references for each order role.
• SOLD_TO_CONTACT_ID, SHIP_TO_CONTACT_ID, INVOICE_TO_CONTACT_ID, and DELIVER_TO_CONTACT_ID — contact references resolving to HZ_CUST_ACCOUNT_ROLES.
• END_CUSTOMER_ID, END_CUSTOMER_CONTACT_ID, and END_CUSTOMER_SITE_USE_ID — end-customer references for channel and indirect order flows.
• PAYMENT_TERM_ID — the payment terms, referencing PN_PAYMENT_TERMS_ALL.
• HIST_TYPE_CODE, HIST_COMMENTS, HIST_CREATED_BY, and HIST_CREATION_DATE — history-specific metadata identifying the type of change, free-text comments, and the user and timestamp of the history record.
• RESPONSIBILITY_ID — the responsibility context under which the change was made.
• LAST_UPDATE_DATE, CREATED_BY, PROGRAM_ID, and REQUEST_ID — standard audit and concurrent program columns.
• CONTEXT, ATTRIBUTE1 through ATTRIBUTE20, and the GLOBAL_ATTRIBUTE and TP_ATTRIBUTE descriptor-flex columns — extensibility fields capturing additional header attributes.

HEADER_ID functions as the primary key component. Business-key candidates include ORDER_NUMBER combined with VERSION_NUMBER, which identifies a specific version of an order header.

Common Use Cases and Queries

The table supports audit and historical reporting scenarios. A typical query retrieves the full change history for a specific order:

• Order change audit — SELECT * FROM ONT.OE_ORDER_HEADER_HISTORY WHERE HEADER_ID = :header_id ORDER BY VERSION_NUMBER, CHANGE_SEQUENCE;
• Attribute-level change tracking — compare FLOW_STATUS_CODE, BOOKED_FLAG, and key date columns across successive VERSION_NUMBER rows to identify when an order moved from entered to booked or was cancelled.
• Historical reporting by operating unit — aggregate ORDER_NUMBER and HIST_TYPE_CODE by ORG_ID and DATE range for change-volume analysis.
• User activity analysis — group by HIST_CREATED_BY and RESPONSIBILITY_ID to determine which users or responsibilities historically modified order headers.
• End-customer and contact resolution — join SOLD_TO_CONTACT_ID and END_CUSTOMER_CONTACT_ID to HZ_CUST_ACCOUNT_ROLES to attribute historical order changes to specific customer contacts.

Because the table holds one row per historical version, queries should always be scoped by HEADER_ID or by a bounded date range to avoid full-scan cost on a large historical set.

Related Objects

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

• OE_ORDER_HEADERS_ALL — the primary order header table; HEADER_ID links history rows to the current header record.
• SO_ORDER_TYPES_115_ALL — referenced by ORDER_TYPE_ID for order type definition.
• PN_PAYMENT_TERMS_ALL — referenced by PAYMENT_TERM_ID for payment terms.
• HZ_CUST_ACCOUNT_ROLES — referenced by SOLD_TO_CONTACT_ID, SHIP_TO_CONTACT_ID, INVOICE_TO_CONTACT_ID, DELIVER_TO_CONTACT_ID, and END_CUSTOMER_CONTACT_ID.
• HZ_CUST_ACCOUNTS — referenced by END_CUSTOMER_ID.
• HZ_CUST_SITE_USES_ALL — referenced by SOLD_TO_SITE_USE_ID and END_CUSTOMER_SITE_USE_ID.
• HZ_CONTACT_POINTS — referenced by SOLD_TO_PHONE_ID.
• IBE_MSITES_B — referenced by MINISITE_ID for iStore minisite context.

The table is primarily read by Order Management inquiry windows, audit reports, and custom historical reporting, and is written by the order header change-history logic within the ONT application.