Search Results hr_h2pi_locations




Overview
Explore in AI Chat

Overview

HR.HR_H2PI_LOCATIONS is a VALID table owned by the HR schema within the PER — Human Resources product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The object functions as a staging table for the Oracle HR to Oracle Payroll Interface (H2PI), storing location-related data while a data upload is in progress. In this capacity, it acts as an intermediate holding area: records are written to this table during the upload cycle and subsequently consumed or validated before the corresponding location records are committed to the production HR tables.

From a heuristic Data Vault modeling perspective, the mined foreign key structure classifies HR_H2PI_LOCATIONS as standalone. This suggests that the table may be modeled as a hub-like or standalone entity rather than a dependent satellite or link, since the metadata does not expose parent-child FK dependencies to other documented tables. The heuristic classification is a modeling suggestion only and should be validated against the actual H2PI integration design before being used as a definitive Data Vault assertion.

The physical schema documented in ETRM 12.2.2 contains 50 columns. The table is constrained by the primary key HR_H2PI_LOCATIONS_PK, defined on the composite of CLIENT_ID, BUSINESS_GROUP_ID, and LOCATION_ID. This composite key reflects the multi-tenant, multi-business-group architecture of Oracle HR, where a single location identifier is meaningful only within the scope of a client and business group.

Key Information Stored

The table holds a broad set of location attributes, of which the following are most operationally significant:

  • CLIENT_ID, BUSINESS_GROUP_ID, LOCATION_ID — the composite surrogate/primary key components defined by HR_H2PI_LOCATIONS_PK. In this design the three columns together form the unique identifier for each staging row; LOCATION_ID is the natural business-key candidate for the location itself, while CLIENT_ID and BUSINESS_GROUP_ID provide the partitioning context.
  • LOCATION_CODE — the user-facing short code that identifies a location within a business group, typically the primary business-key candidate used in lookups and interfaces.
  • DESCRIPTION — descriptive text for the location record.
  • ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 — structured street address fields for the location.
  • TOWN_OR_CITY, COUNTRY, POSTAL_CODE — geographic and postal attributes used for statutory and reporting purposes.
  • REGION_1, REGION_2, REGION_3 — flexible region fields supporting country-specific address formats.
  • TELEPHONE_NUMBER_1, TELEPHONE_NUMBER_2, TELEPHONE_NUMBER_3 — contact telephone numbers associated with the location.
  • INACTIVE_DATE — the date on which the location becomes or became inactive, supporting date-effective processing.
  • STYLE — the address style indicator that determines which address format and region fields apply.
  • STATUS — the processing status of the staging row during the H2PI upload lifecycle.

The remaining columns consist of LOC_INFORMATION13 through LOC_INFORMATION20, ATTRIBUTE_CATEGORY, ATTRIBUTE1 through ATTRIBUTE20, and LAST_UPD_DATE. The ATTRIBUTE and LOC_INFORMATION columns are descriptive flexfield segments, providing extensibility for customer-specific location data captured during the interface.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting the H2PI location upload. Because the table is a staging area, queries typically focus on rows that have not yet been processed or that carry an unexpected STATUS value:

  • Identify pending or problem rows for a business group: SELECT LOCATION_ID, LOCATION_CODE, STATUS FROM HR.HR_H2PI_LOCATIONS WHERE BUSINESS_GROUP_ID = :p_bg_id AND STATUS IS NULL;
  • Reconcile staging against production locations by joining on the business key: SELECT s.LOCATION_ID, s.LOCATION_CODE, p.LOCATION_ID FROM HR.HR_H2PI_LOCATIONS s, HR.HR_LOCATIONS_ALL p WHERE s.CLIENT_ID = p.CLIENT_ID AND s.BUSINESS_GROUP_ID = p.BUSINESS_GROUP_ID AND s.LOCATION_ID = p.LOCATION_ID;
  • Audit recently loaded location data using LAST_UPD_DATE: SELECT LOCATION_CODE, STATUS, LAST_UPD_DATE FROM HR.HR_H2PI_LOCATIONS WHERE LAST_UPD_DATE > SYSDATE - 7;
  • Reporting on address completeness before payroll processing, checking for null ADDRESS_LINE_1, TOWN_OR_CITY, or COUNTRY values.

These patterns support functional analysts validating upload results, DBAs diagnosing interface failures, and developers building reconciliation reports.

Related Objects

Although the mined relationship data classifies HR_H2PI_LOCATIONS as standalone, the H2PI integration design implies linkage to the following objects, joined on the documented key columns:

  • HR_LOCATIONS_ALL — the production location table; joined on CLIENT_ID, BUSINESS_GROUP_ID, and LOCATION_ID. This is the principal destination of the staged data.
  • HR_ORGANIZATION_UNITS — organizations reference locations by LOCATION_ID and BUSINESS_GROUP_ID.
  • HR_ALL_ORGANIZATION_UNITS — the base organization view that resolves location assignments.
  • PER_BUSINESS_GROUPS — defines the BUSINESS_GROUP_ID context used in the composite key.
  • PAY_LOCATION_DETAILS / payroll location objects — consumed by the payroll side of the H2PI interface.
  • H2PI staging companions — such as HR_H2PI_* tables for organizations, people, and assignments, which follow the same CLIENT_ID / BUSINESS_GROUP_ID staging convention.

Direct API access is generally through the H2PI concurrent programs rather than through this table, which should be treated as an internal interface staging structure.