Search Results hr_dm_resolve_pks_pk

Overview

HR.HR_DM_RESOLVE_PKS is a technical support table within the Oracle E-Business Suite Human Resources (HR) schema. It is documented in the ETRM as a VALID table owned by HR, with FND Design Data reference PER.HR_DM_RESOLVE_PKS. The table stores the values of the primary key for both the source and destination database for each table, specifically for use by the TUPS (Table Update Propagation Services) package. In this role, it functions as a cross-database key-resolution registry that allows the DataMerge/DataMover style utilities to map a source system's primary key value to the corresponding destination system's primary key value, table by table and database instance by database instance.

The object is physically stored in the APPS_TS_INTERFACE tablespace with PCT Free 10, reflecting its role as an interface/staging structure rather than a transactional master. Its Data Vault classification, mined heuristically from the foreign-key structure, is standalone; this should be treated as a modeling suggestion rather than a canonical designation, since the table carries its own surrogate key and only sparse outbound foreign keys.

Key Information Stored

The table contains 11 documented columns. The most consequential are listed below, distinguishing the system surrogate from the business-key candidates declared through unique indexes.

• RESOLVE_PK_ID (NUMBER(15), mandatory) — System-generated primary key column. It forms the unique index HR_DM_RESOLVE_PKS_PK.
• TABLE_NAME (VARCHAR2(30)) — Name of the table whose primary key is being resolved. Part of the business-key candidate index HR_DM_RESOLVE_PKS_UK1.
• SOURCE_ID (NUMBER(15)) — The value of the primary key for the table in the source database. Part of HR_DM_RESOLVE_PKS_UK1.
• DESTINATION_ID (NUMBER(15)) — The value of the primary key for the table in the destination database, completing the source-to-destination mapping pair.
• SOURCE_DATABASE_INSTANCE (VARCHAR2(30)) — Name of the source database for which the primary key resolution information applies. Part of HR_DM_RESOLVE_PKS_UK1.
• SECURITY_GROUP_ID (NUMBER(15)) — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org / security group partitioning.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who columns recording audit and accountability metadata.

The composite unique index HR_DM_RESOLVE_PKS_UK1 (TABLE_NAME, SOURCE_ID, SOURCE_DATABASE_INSTANCE) is the documented business-key candidate; it guarantees that any given source key from a given source database resolves to at most one row per table.

Common Use Cases and Queries

Typical usage centers on key translation during data propagation, reconciliation of source and destination identifiers, and auditing incomplete mappings. A resolution lookup for a specific table and source instance follows this pattern:

• Resolve a single key: SELECT DESTINATION_ID FROM HR.HR_DM_RESOLVE_PKS WHERE TABLE_NAME = :p_table AND SOURCE_ID = :p_source_id AND SOURCE_DATABASE_INSTANCE = :p_instance;
• Count resolved versus unresolved mappings per table: SELECT TABLE_NAME, COUNT(*) FROM HR.HR_DM_RESOLVE_PKS GROUP BY TABLE_NAME;
• Review recent changes through Who columns: SELECT RESOLVE_PK_ID, TABLE_NAME, SOURCE_ID, DESTINATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY FROM HR.HR_DM_RESOLVE_PKS ORDER BY LAST_UPDATE_DATE DESC;
• Multi-org aware reporting scoped by security group: filter on SECURITY_GROUP_ID when queries run across multiple security groups.

The general extraction query documented in ETRM selects RESOLVE_PK_ID, TABLE_NAME, SOURCE_ID, DESTINATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, SOURCE_DATABASE_INSTANCE, and SECURITY_GROUP_ID from HR.HR_DM_RESOLVE_PKS.

Related Objects

The documented dependencies and foreign keys identify the following significant related objects:

• HR.HR_DM_RESOLVE_PKS# — The trigger/implementation object referencing HR_DM_RESOLVE_PKS within the HR schema.
• FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID; governs row visibility in a multi-org context.
• IGI_DOS_DESTINATIONS — Referenced by the DESTINATION_ID foreign key, linking resolution targets to destination definitions.
• TUPS package — The consumer documented in ETRM as the primary user of this table's key-resolution data.
• HR.HR_DM_RESOLVE_PKS_PK / HR_DM_RESOLVE_PKS_UK1 — The supporting unique indexes on the surrogate and business keys respectively.

No additional outbound references are documented; HR.HR_DM_RESOLVE_PKS does not reference any other database object beyond the foreign keys noted above.