Results for “oe_blanket_lines_hist”

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

Overview

OE_BLANKET_LINES_HIST is an Order Management (ONT) transactional history table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores historical versions of sales agreement (blanket order) line records. Whenever a blanket sales agreement line is created, revised, versioned, or otherwise modified, the prior state of the line is written to this table. The companion operational table OE_BLANKET_LINES holds the current active definition of a sales agreement line; OE_BLANKET_LINES_HIST preserves the audit trail of how that line evolved over time, including amount and quantity controls, release accumulators, effective dates, and pricing modifiers.

The table is owned by the ONT schema and contains 372 documented columns, a very large column footprint that is explained by its design: the history table mirrors the full column structure of the operational blanket line, then adds history-specific control columns such as HIST_TYPE_CODE, HIST_COMMENTS, HIST_CREATION_DATE, and HIST_CREATED_BY, along with versioning attributes (BLANKET_VERSION_NUMBER, VERSION_NUMBER, VERSION_FLAG, PHASE_CHANGE_FLAG). Because it records point-in-time snapshots, it supports audit, reconciliation, and agreement-version reporting rather than day-to-day order entry processing.

From a dimensional modeling perspective, the heuristic Data Vault classification for this object is link. This classification is driven by the foreign key topology, where the table functions as a join point connecting header/line context to customer contact roles and reference entities. Analysts constructing a Data Vault or normalized reporting layer should treat it as a link table, with hubs for the referenced business entities and satellites for the descriptive and historical attributes.

Key Information Stored

The table's identity and history-tracking columns are of primary interest:

• LINE_ID — the surrogate key inherited from the operational blanket line. In the history context it is paired with the versioning columns to identify a specific historical snapshot, rather than being globally unique on its own.
• HEADER_ID and AGREEMENT_ID — the parent blanket agreement/header context for the historical line.
• HIST_TYPE_CODE, HIST_COMMENTS, HIST_CREATION_DATE, and HIST_CREATED_BY — the history-specific audit columns. HIST_TYPE_CODE identifies the nature of the change that produced the snapshot, and these are the columns that distinguish this table from the operational line table.
• BLANKET_NUMBER, BLANKET_LINE_NUMBER, and BLANKET_VERSION_NUMBER — business-facing identifiers of the agreement and line, plus the version of the blanket against which the line was captured.
• VERSION_NUMBER, VERSION_FLAG, and PHASE_CHANGE_FLAG — version and phase indicators that describe where the line sat in the agreement lifecycle at the time of the snapshot.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective period of the historical line version.
• BLANKET_LINE_MAX_AMOUNT, BLANKET_LINE_MIN_AMOUNT, MAX_RELEASE_AMOUNT, MIN_RELEASE_AMOUNT, MAX_RELEASE_QUANTITY, and MIN_RELEASE_QUANTITY — release controls captured at each version.
• RELEASED_AMOUNT, RELEASED_QUANTITY, RETURNED_AMOUNT, RETURNED_QUANTITY, and FULFILLED_AMOUNT — accumulators reflecting consumption against the line at snapshot time.
• INVENTORY_ITEM_ID, ORDERED_ITEM, ORDERED_QUANTITY_UOM, UNIT_LIST_PRICE, and UNIT_SELLING_PRICE — the item and pricing context of the historical line.
• INST_ID — the Multi-Org / instance identifier supporting operating unit and instance-level data segregation.

Because the table is a history/link structure, uniqueness is conventionally enforced through the combination of the line identifier with the version and history creation attributes rather than by LINE_ID alone; consumers should not assume a single row per LINE_ID.

Common Use Cases and Queries

The dominant use cases are agreement audit, version comparison, and release-control analysis. Typical reporting scenarios include reconstructing the state of a blanket line at a prior point in time, comparing negotiated controls across versions, and tracing who changed release limits and when.

A representative query to retrieve the version history of a line:

• SELECT line_id, blanket_number, blanket_line_number, blanket_version_number, version_number, start_date_active, end_date_active, blanket_line_max_amount, blanket_line_min_amount, hist_type_code, hist_creation_date, hist_created_by FROM oe_blanket_lines_hist WHERE blanket_number = :p_blanket_number ORDER BY blanket_line_number, version_number, hist_creation_date;

A second common pattern identifies lines whose maximum release amount changed across versions:

• SELECT h.line_id, h.version_number, h.max_release_amount, h.hist_creation_date FROM oe_blanket_lines_hist h WHERE h.blanket_number = :p_blanket_number AND h.max_release_amount IS NOT NULL ORDER BY h.line_id, h.version_number;

Reporting builds frequently join this table back to the customer account role table on the contact role columns (SHIP_TO_CONTACT_ID, INTMED_SHIP_TO_CONTACT_ID, INVOICE_TO_CONTACT_ID, DELIVER_TO_CONTACT_ID) to resolve the parties associated with each historical version, and to the item master for item descriptions. Because the table is large and append-oriented, queries should always be filtered by agreement identifier, blanket number, or date range to avoid full scans.

Related Objects

• HZ_CUST_ACCOUNT_ROLES — referenced four times: SHIP_TO_CONTACT_ID, INTMED_SHIP_TO_CONTACT_ID, INVOICE_TO_CONTACT_ID, and DELIVER_TO_CONTACT_ID all join to this table to resolve contact roles for each historical version.
• PO_LINE_TYPES_B — referenced through LINE_TYPE_ID, supplying the line type definition for the historical line.
• PN_PAYMENT_TERMS_ALL — referenced through PAYMENT_TERM_ID, providing payment term context.
• PON_OFFER_COMMITMENTS — referenced through COMMITMENT_ID, linking the line to its purchasing commitment.
• MTL_CUSTOMER_ITEMS — referenced through CUSTOMER_ITEM_ID, resolving the customer item definition used on the line.
• OE_BLANKET_LINES — the operational counterpart holding the current line definition; history rows are derived from changes to this table and are typically compared against it.
• OE_BLANKET_HEADERS_HIST and OE_ORDER_HEADERS_ALL — header-level context for the agreement, joined through HEADER_ID and AGREEMENT_ID.

Together these relationships make OE_BLANKET_LINES_HIST the authoritative audit trail for sales agreement line evolution in Oracle Order Management.