Search Results xle_histories




Overview
Explore in AI Chat

Overview

XLE_HISTORIES is a table owned by the XLE schema (Legal Entity Configurator) within Oracle E-Business Suite 12.1.1 and 12.2.2. As documented in the ETRM metadata, it stores table and column names for tracked Registration attributes, functioning as a repository of attribute-level change history for registration data managed through the Legal Entity Configurator. It supports auditability by capturing the before-and-after values of specific columns on specific source records, along with the effective dating window during which each value applied.

From a data modeling perspective, the heuristic Data Vault classification mined from the foreign key structure is standalone. This indicates that XLE_HISTORIES does not participate in a classic hub/link/satellite network through declared foreign keys other than its link to CS_KB_HISTORIES_B; it behaves as an independent history/audit store keyed on its own surrogate identifier. The single documented foreign key — HISTORY_ID referencing CS_KB_HISTORIES_B — anchors each row to the underlying history framework rather than to a business entity hub.

Key Information Stored

The table is documented with 14 columns in the 12.2.2 physical schema. The most significant are listed below.

  • HISTORY_ID — The surrogate primary key and the sole unique index candidate (XLE_HISTORIES_U1). It also serves as the foreign key to CS_KB_HISTORIES_B, linking each tracked attribute change to the shared history framework.
  • SOURCE_TABLE — Identifies the source table whose attribute is being tracked.
  • SOURCE_ID — Identifies the specific source row (business record) within SOURCE_TABLE.
  • SOURCE_COLUMN_NAME — Names the column on the source table whose value is being tracked.
  • SOURCE_COLUMN_VALUE — Holds the captured value of that column at the point of the change.
  • EFFECTIVE_FROM / EFFECTIVE_TO — Define the date range during which the recorded value was in effect, enabling point-in-time reconstruction of registration attributes.
  • COMMENTS — Free-text annotation describing the change.
  • OBJECT_VERSION_NUMBER — Supports optimistic locking and concurrent update control.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture standard EBS "who/when" audit information.

The surrogate key (HISTORY_ID) is distinct from the business-key candidate declared by the unique index XLE_HISTORIES_U1, which also covers HISTORY_ID. No additional compound business keys are documented.

Common Use Cases and Queries

Typical scenarios include analyzing how a registration attribute changed over time, reconstructing the value that applied on a given date, and auditing who modified tracked attributes. A representative query to retrieve the full change set for one source record is:

  • SELECT h.HISTORY_ID, h.SOURCE_TABLE, h.SOURCE_ID, h.SOURCE_COLUMN_NAME, h.SOURCE_COLUMN_VALUE, h.EFFECTIVE_FROM, h.EFFECTIVE_TO FROM XLE_HISTORIES h WHERE h.SOURCE_TABLE = :p_table AND h.SOURCE_ID = :p_id ORDER BY h.EFFECTIVE_FROM;
  • A point-in-time query adds the predicate: AND :as_of_date BETWEEN h.EFFECTIVE_FROM AND NVL(h.EFFECTIVE_TO, :as_of_date).
  • An audit trail report joins LAST_UPDATED_BY to per_all_people_f or fnd_user to attribute each change to an operator.

Because the GUIDs and business keys live on the source records, reporting should join SOURCE_TABLE/SOURCE_ID back to the originating entity to obtain meaningful context.

Related Objects

  • CS_KB_HISTORIES_B — Referenced by XLE_HISTORIES.HISTORY_ID; the parent history framework table.
  • XLE_REGISTRATIONS (and related XLE registration objects) — The most likely SOURCE_TABLE targets whose tracked attributes populate SOURCE_COLUMN_NAME and SOURCE_COLUMN_VALUE.
  • XLE_ENTITY_PROFILES / XLE_ETB_PROFILES — Legal entity configuration tables whose changes may be reflected in tracked attributes.
  • FND_USER and PER_ALL_PEOPLE_F — Joined on LAST_UPDATED_BY/CREATED_BY for user attribution in audit reporting.

These objects, together with the XLE Legal Entity Configurator APIs that write registration changes, form the operational context for XLE_HISTORIES.