Search Results hr_h2pi_id_mapping




Overview
Explore in AI Chat

Overview

The HR.HR_H2PI_ID_MAPPING table is a Human Resources (PER) module object that stores identifier mapping information used by the Oracle HR to Oracle Payroll Interface, commonly referred to as H2PI. Its purpose is to correlate identifiers held in Oracle HR with the corresponding identifiers expected by the downstream payroll interface, ensuring that records exchanged between the two systems resolve to the correct business entity.

The table resides in the HR schema and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. The documented physical schema in ETRM 12.2.2 lists ten columns, with the primary key defined by the constraint HR_H2PI_ID_MAPPING_PK over the column combination (FROM_ID, TO_BUSINESS_GROUP_ID, TABLE_NAME). This composite key establishes the mapping grain: a source identifier is unique within a given business group and source table name.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure is standalone. This suggests the table functions as an independent reference or mapping construct rather than participating as a classic hub, link, or satellite within a normalized model. The designation reflects the absence of documented foreign key relationships to other tables, indicating that referential integrity is maintained by application logic rather than declarative constraints.

Key Information Stored

The ten documented columns form a compact mapping structure. The most significant columns and their roles are as follows:

  • FROM_ID — The source identifier being mapped. It participates in the composite primary key and represents the value originating from the HR side of the interface.
  • TO_ID — The target identifier to which the source value is mapped, typically the identifier recognized by the payroll interface.
  • TABLE_NAME — The name of the source table whose identifier is being mapped. This is a primary key component and disambiguates identifiers that may collide across different entities.
  • TO_BUSINESS_GROUP_ID — The business group context for the target identifier. It is the third primary key component, scoping mappings to a specific business group.
  • ID_COLUMN_NAME — The specific column name holding the identifier, providing finer granularity than TABLE_NAME alone.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns recording when and by whom the mapping row was last modified.
  • CREATED_BY, CREATION_DATE — Standard WHO audit columns recording the creation of the mapping row.

The surrogate-style unique key is the composite primary key HR_H2PI_ID_MAPPING_PK; no separate single-column unique index is documented. Business-key candidates are therefore the tuple of FROM_ID, TO_BUSINESS_GROUP_ID, and TABLE_NAME.

Common Use Cases and Queries

Typical usage centers on resolving identifiers during H2PI data exchange and on auditing mappings for reconciliation. A representative query retrieving the target identifier for a known source value is:

  • SELECT TO_ID, ID_COLUMN_NAME FROM HR.HR_H2PI_ID_MAPPING WHERE FROM_ID = :p_from_id AND TO_BUSINESS_GROUP_ID = :p_bg_id AND TABLE_NAME = :p_table_name;
  • Reporting all mappings for a business group: SELECT TABLE_NAME, FROM_ID, TO_ID FROM HR.HR_H2PI_ID_MAPPING WHERE TO_BUSINESS_GROUP_ID = :p_bg_id ORDER BY TABLE_NAME, FROM_ID;
  • Auditing recent changes: SELECT FROM_ID, TO_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY FROM HR.HR_H2PI_ID_MAPPING WHERE LAST_UPDATE_DATE > :p_since;

These patterns support troubleshooting failed interface runs, verifying that payroll records reference valid HR entities, and producing reconciliation reports between the two systems.

Related Objects

The documented metadata classifies this table as standalone, with no declarative foreign keys linking it to parent or child objects. Dependencies are therefore functional rather than enforced. The objects most commonly associated with HR_H2PI_ID_MAPPING are those participating in the H2PI integration flow, including the HR to Payroll interface concurrent programs and the underlying PER entity tables whose identifiers are referenced by FROM_ID and TABLE_NAME. Because the metadata does not enumerate specific FK relationships, join columns against PER business group and person-related tables are established through the TO_BUSINESS_GROUP_ID and FROM_ID values rather than database constraints. Administrators should treat the composite primary key as the authoritative join anchor when correlating this table with H2PI staging and interface objects.