Search Results hr_dm_sequence_histories_pk

Overview

The HR.HR_DM_SEQUENCE_HISTORIES table is an interface-layer repository within the Oracle E-Business Suite HR schema that records historical sequence information sourced from the various Fidelity databases integrated with Oracle EBS. It forms part of the data migration and integration infrastructure used to synchronize and track sequence consumption across disparate database environments. The table resides in the APPS_TS_INTERFACE tablespace, which is consistent with its role as a staging and tracking structure rather than a transactional production table.

In the context of Oracle EBS 12.1.1 and 12.2.2, this object supports the periodic capture of sequence values so that subsequent changes to sequences can be made against a verifiable historical baseline. The FND Design Data reference PER.HR_DM_SEQUENCE_HISTORIES confirms its registration as a database object within the Oracle Applications data model, and its VALID status indicates an active, supported structure in the documented environment.

From a Data Vault modeling perspective, the metadata suggests a classification of this object as a link. The presence of two foreign keys — one to HR_DM_SEQUENCE_DEFINITIONS and one to HR_DM_DATABASES — indicates that the table primarily records associations between sequence definitions and database instances at a point in time, which is characteristic of a link structure that resolves a many-to-many relationship between these two entities. The addition of a TIMESTAMP attribute further qualifies it as a temporal link capturing the state of that relationship at a specific moment.

Key Information Stored

The table is anchored by the surrogate primary key SEQUENCE_HISTORY_ID, a system-generated NUMBER(15) column that uniquely identifies each historical record. This column is enforced through the unique index HR_DM_SEQUENCE_HISTORIES_PK and serves as the definitive identifier for any given history row.

A second unique index, HR_DM_SEQUENCE_HISTORIES_UK1, spans the business-key candidate columns SEQUENCE_ID, DATABASE_ID, and TIMESTAMP. This composite uniqueness constraint ensures that no duplicate observation exists for a given sequence on a given database at a given time, which is the natural business identity of the record.

• SEQUENCE_ID — Foreign key to HR_DM_SEQUENCE_DEFINITIONS.SEQUENCE_ID, identifying which sequence definition the historical observation pertains to.
• DATABASE_ID — Foreign key to HR_DM_DATABASES.DATABASE_ID, identifying the source Fidelity database from which the sequence value was read.
• TIMESTAMP — VARCHAR2(14) recording the moment the sequence value was captured, formatted as YYYYMMDDHH24MISS.
• SEQUENCE_VALUE — NUMBER(30) holding the value of the last number drawn from the sequence, sourced from the LAST_NUMBER column of ALL_SEQUENCES. This value may exceed the true current sequence position by as much as the sequence cache setting.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who columns capturing audit metadata about the most recent modification.
• CREATED_BY, CREATION_DATE — Standard Who columns recording the creation context of the row.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-organization security access at the row level.

Common Use Cases and Queries

The principal use case for this table is audit and reconciliation of sequence consumption across multiple integrated databases. Administrators can determine the last captured value for a sequence on any registered database prior to performing sequence alterations, thereby preserving an auditable trail.

A typical reporting query retrieves the most recent history entry for each sequence and database combination:

• SELECT sequence_id, database_id, MAX(timestamp) FROM hr_dm_sequence_histories GROUP BY sequence_id, database_id;
• Joining to HR_DM_SEQUENCE_DEFINITIONS and HR_DM_DATABASES to resolve sequence names and database identifiers for readout reporting.
• Identifying gaps or stale entries where no history has been captured within an expected interval, which may indicate a failed or skipped integration run.
• Validating that SEQUENCE_VALUE values are monotonically increasing across successive TIMESTAMP entries for the same SEQUENCE_ID and DATABASE_ID pair.

Because the table is an interface object, it is frequently accessed by concurrent programs and data migration scripts rather than by end-user forms.

Related Objects

• HR.HR_DM_SEQUENCE_DEFINITIONS — Referenced via SEQUENCE_ID; defines the sequences being tracked.
• HR.HR_DM_DATABASES — Referenced via DATABASE_ID; registers the source Fidelity databases.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID; governs security group access.
• APPS.ALL_SEQUENCES — Source view from which SEQUENCE_VALUE is populated via LAST_NUMBER.
• HR_DM_SEQUENCE_HISTORIES_PK, HR_DM_SEQUENCE_HISTORIES_UK1, HR_DM_SEQUENCE_HISTORIES_FK2 — Supporting indexes that enforce integrity and accelerate joins.