Search Results hr_dm_sequence_histories_fk2

Overview

HR.HR_DM_SEQUENCE_HISTORIES is an interface-staging table in the Oracle E-Business Suite HR schema. It stores sequence information harvested from the various Fidelity databases participating in an HR data migration, and it is designed to be populated before any changes are made to production sequences, so that a recoverable history of sequence state is preserved for sequence history tracking. The table resides in the APPS_TS_INTERFACE tablespace with a PCT Free of 10, and its FND Design Data reference is PER.HR_DM_SEQUENCE_HISTORIES, confirming its role as part of the HR data migration (HR_DM) object family rather than core transactional HR processing.

Under a heuristic Data Vault classification mined from the foreign key structure, the object is best modeled as a link. It resolves the many-to-many association between a sequence definition and a database instance at a point in time, which is precisely the pattern of a link table in a Data Vault model. The classification is a modeling suggestion derived from FK structure, not a formal Oracle designation.

Key Information Stored

The table carries eleven documented columns. The most significant are:

• SEQUENCE_HISTORY_ID — NUMBER(15), system-generated surrogate primary key, enforced by the unique index HR_DM_SEQUENCE_HISTORIES_PK.
• SEQUENCE_ID — NUMBER(15), foreign key to HR_DM_SEQUENCE_DEFINITIONS.SEQUENCE_ID, identifying which sequence the history row describes.
• DATABASE_ID — NUMBER(15), foreign key to HR_DM_DATABASES.DATABASE_ID, identifying the source Fidelity database holding that sequence.
• TIMESTAMP — VARCHAR2(14), the time at which the sequence value was updated, stored in YYYYMMDDHH24MISS format.
• SEQUENCE_VALUE — NUMBER(30), the last number used from the sequence, read from LAST_NUMBER in ALL_SEQUENCES. This value may exceed the true current sequence value, or the cache maxvalue, by up to the sequence cache setting.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE, providing audit and concurrency context.
• SECURITY_GROUP_ID — the security grouping column, with a documented foreign key relationship to FND_SECURITY_GROUPS.

Two unique indexes act as business-key candidates. HR_DM_SEQUENCE_HISTORIES_PK is the true surrogate primary key on SEQUENCE_HISTORY_ID. HR_DM_SEQUENCE_HISTORIES_UK1 is a composite unique key on (SEQUENCE_ID, DATABASE_ID, TIMESTAMP) — the natural identifier that prevents duplicate snapshots of the same sequence on the same database at the same instant. A non-unique index, HR_DM_SEQUENCE_HISTORIES_FK2, supports DATABASE_ID access.

Common Use Cases and Queries

The primary use case is pre-migration sequence preservation. Before altering a sequence, the migration process inserts the current LAST_NUMBER so the state is recoverable. Reference queries typically resolve the composite business key or audit a sequence over time:

• Locate a specific historical snapshot: SELECT * FROM hr.hr_dm_sequence_histories WHERE sequence_id = :p_seq AND database_id = :p_db AND timestamp = :p_ts;
• Report the latest recorded value per sequence and database, using the composite unique key ordering: SELECT sequence_id, database_id, MAX(timestamp) FROM hr.hr_dm_sequence_histories GROUP BY sequence_id, database_id;
• Join to HR_DM_SEQUENCE_DEFINITIONS and HR_DM_DATABASES to obtain human-readable sequence and database names for reconciliation reports.
• Audit who performed a sequence capture, using the Standard Who columns.

Because all indexes live in APPS_TS_INTERFACE, reporting against this table is normally confined to migration and reconciliation windows rather than online transactional workloads.

Related Objects

• HR.HR_DM_SEQUENCE_DEFINITIONS — joined on SEQUENCE_ID; defines the sequences being tracked.
• HR.HR_DM_DATABASES — joined on DATABASE_ID; identifies each Fidelity source database.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; governs row-level access by security group.
• ALL_SEQUENCES — the data dictionary view from which the SEQUENCE_VALUE (LAST_NUMBER) is read.
• HR.HR_DM_SEQUENCE_HISTORIES_PK / _UK1 / _FK2 — the indexes supporting primary, business-key, and foreign-key access paths.
• Sibling HR_DM interface tables that follow the same staging and Who-column conventions for migration loading and validation.