Results for “hr_h2pi_assignments”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR.HR_H2PI_ASSIGNMENTS is a staging table owned by the HR schema within the PER — Human Resources product of Oracle E-Business Suite (validated against 12.1.1 and 12.2.2). It supports the Oracle HR to Oracle Payroll Interface (H2PI), the batch mechanism that transfers assignment-level data from the HRMS core tables into the Oracle Payroll application. The table holds assignment records transiently while a data upload is in progress, meaning rows are inserted during the interface run, consumed by downstream payroll processing, and typically cleared or purged once the transfer completes. It is not a system of record; it is a landing zone whose contents mirror the structure of PER_ALL_ASSIGNMENTS_F and related assignment entities for the duration of the H2PI cycle.

Mined from its foreign-key structure, the table is heuristically classified as a standalone data vault object rather than a hub, link, or satellite. This classification reflects that HR_H2PI_ASSIGNMENTS does not participate in the hub-and-link topology of the core HRMS model; instead it is a denormalized, self-contained staging entity. The documented physical schema for 12.2.2 lists 112 columns, and a composite primary key named HR_H2PI_ASSIGNMETNS_PK (note the documented spelling) governs uniqueness.

Key Information Stored

The composite primary key comprises ASSIGNMENT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID, and CLIENT_ID. Of these, ASSIGNMENT_ID is the surrogate identifier of the source assignment, while BUSINESS_GROUP_ID and CLIENT_ID are multi-tenancy discriminators; together with the effective dating pair they form the business-key candidate for uniqueness. The most significant columns include:

• ASSIGNMENT_ID — identifier of the assignment being staged; primary component of the primary key.
• PERSON_ID — the person to whom the assignment belongs.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity window of the assignment row.
• BUSINESS_GROUP_ID and CLIENT_ID — business group and client partition keys.
• ASSIGNMENT_NUMBER — the user-visible assignment number.
• PRIMARY_FLAG — indicates whether this is the person's primary assignment.
• PAY_BASIS_ID — foreign key to PER_PAY_BASES defining the pay basis.
• PAYROLL_ID and LOCATION_ID and ORGANIZATION_ID — the payroll, location, and organization context.
• NORMAL_HOURS and FREQUENCY — standard working hours and payroll frequency.
• HOURLY_SALARIED_CODE and EMPLOYMENT_CATEGORY — classification attributes.
• STATUS — processing status of the staged row.
• SEGMENT1–SEGMENT30 and PPG_SEGMENT1–PPG_SEGMENT30 — key flexfield and payroll People Group Flexfield segment columns.
• ASS_ATTRIBUTE1–ASS_ATTRIBUTE30 and ASS_ATTRIBUTE_CATEGORY — descriptive flexfield context and values.

Common Use Cases and Queries

The primary operational use case is monitoring and troubleshooting the HR-to-Payroll transfer. DBAs and payroll administrators inspect staged rows to confirm what has been queued, identify records stuck in a given STATUS, and reconcile counts against the source assignment table. Typical queries include:

• Counting staged assignments by business group: SELECT BUSINESS_GROUP_ID, COUNT(*) FROM HR.HR_H2PI_ASSIGNMENTS GROUP BY BUSINESS_GROUP_ID;
• Retrieving a specific assignment's staged rows: SELECT * FROM HR.HR_H2PI_ASSIGNMENTS WHERE ASSIGNMENT_ID = :id;
• Joining back to the core assignment table to compare staged versus live values on ASSIGNMENT_ID and effective dates.
• Verifying payroll linkage via PAY_BASIS_ID against PER_PAY_BASES and PAYROLL_ID against the payroll definition.
• Reporting on flexfield content (SEGMENT and PPG_SEGMENT columns) to confirm correct payroll costing and People Group derivation before the interface consumes the rows.

Because the table is transient, reporting should focus on reconciliation and error diagnosis rather than historical trending.

Related Objects

The documented foreign key relates PAY_BASIS_ID to PER_PAY_BASES. In practice the following objects are most significant for joins and dependency analysis:

• PER_PAY_BASES — referenced via PAY_BASIS_ID.
• PER_ALL_ASSIGNMENTS_F — source of assignment data; join on ASSIGNMENT_ID and overlapping effective dates.
• PER_ALL_PEOPLE_F — person attributes; join on PERSON_ID.
• PAY_PAYROLLS / PAY_PAYROLLS_F — payroll definitions; join on PAYROLL_ID.
• HR_ALL_ORGANIZATION_UNITS — organization context; join on ORGANIZATION_ID.
• HR_LOCATIONS_ALL — location context; join on LOCATION_ID.
• PER_ASSIGNMENT_STATUS_TYPES and related lookups — for interpreting STATUS and classification codes.
• The H2PI concurrent programs and payroll interface APIs that consume and purge rows from this staging table.