Search Results hr_h2pi_addresses




Overview
Explore in AI Chat

Overview

HR_H2PI_ADDRESSES is a staging table owned by the HR schema in Oracle E-Business Suite, operating within the PER (Human Resources) product family. It exists to support the Oracle HR to Oracle Payroll Interface (H2PI) — a batch-oriented data transport layer that moves person and address information from the HRMS core tables into the Payroll application. Rather than writing directly into production address structures during an upload, the H2PI process first deposits incoming records into this table while the upload is in progress, allowing validation, transformation, and error handling to occur before the data is finally committed to the target Payroll schema.

Because the table holds transient, process-scoped data rather than enduring business entities, the ETRM heuristic Data Vault classification is standalone. In Data Vault modeling terms, this suggests treating the object as neither a conventional hub nor a durable satellite: no mined foreign-key relationships to other hubs were identified, so it is best modeled as a staging or work table outside the core Data Vault structure. Its transient nature reinforces this — rows are meaningful only during an H2PI upload cycle.

Key Information Stored

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

The surrogate primary key is HR_H2PI_ADDRESSES_PK, defined over the composite of ADDRESS_ID, BUSINESS_GROUP_ID, and CLIENT_ID. These three columns therefore serve as the documented unique identifier for each staged row and act as the primary business-key candidates. BUSINESS_GROUP_ID scopes the record to a specific HR business group, while CLIENT_ID identifies the interface client or process instance. Notably, no separate system-generated surrogate (such as a sequence-based ID) is documented — the natural keys double as the primary key.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting H2PI address uploads. DBAs and payroll interface administrators query the table to confirm that addresses were staged, to identify rows that failed validation, and to reconcile staged counts against rows ultimately pushed to Payroll.

A typical query retrieves pending or errored staging rows for a given business group and client:

SELECT address_id, person_id, address_line1, town_or_city, postal_code, status, last_upd_date
FROM hr.hr_h2pi_addresses
WHERE business_group_id = :p_bg_id
  AND client_id = :p_client_id
  AND status = 'ERROR';

Reporting scenarios include auditing the volume of addresses processed per upload run, checking effective-date coverage via DATE_FROM and DATE_TO, and extracting address segments for downstream reconciliation. Because the table is transient, it is commonly truncated or purged after a successful interface cycle, so reporting should be scheduled to coincide with active upload windows.

Related Objects

The ETRM metadata records no mined foreign-key relationships for this object, consistent with its standalone classification. The following related objects are nonetheless significant in practice:

  • HR.HR_H2PI_PERSONS — the companion H2PI staging table for person data; joined on PERSON_ID (and scoped by business group).
  • HR.HR_H2PI_PERSON_TYPES — staging table holding person type assignments for the same upload run.
  • HR.HR_H2PI_PAYROLLS — staging table for payroll assignments linked to the same interface batch.
  • PER_ADDRESSES — the production HRMS address table that receives validated records after processing.
  • PER_ALL_PEOPLE_F — the primary person table, referenced via PERSON_ID.
  • PAY_ADDRESSES (Payroll schema) — the target address structure written by the H2PI process.
  • HR.HR_H2PI_PROCESS_RUNS / H2PI concurrent programs — the batch processes that populate and consume this staging table.

All references should be validated against the specific 12.1.1 or 12.2.2 instance, as column availability and H2PI object naming may vary by patch level.