Search Results hr_h2pi_element_links_pk

Overview

The HR.HR_H2PI_ELEMENT_LINKS table is a Human Resources (PER) module object within the Oracle E-Business Suite 12.1.1 and 12.2.2 schemas. It stores the effective-dated association between a payroll element and the specific payroll, organization, location, or pay basis to which that element applies. In Oracle Payroll, element links determine eligibility — that is, which assignments, payrolls, or organizational units can receive a given element entry. Without a valid element link, an element cannot be processed even if it is defined at the business group level.

From a Data Vault modeling perspective, the ETRM heuristic classifies this table as standalone. This suggests it functions closer to a hub or reference structure rather than a dependent satellite or a pure transactional link, since it carries its own surrogate key and is not modeled as an association between two hubs in the mined FK structure.

Key Information Stored

The table is defined by the primary key HR_H2PI_ELEMENT_LINKS_PK, which spans ELEMENT_LINK_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID, and CLIENT_ID. The most significant columns include:

• ELEMENT_LINK_ID — surrogate identifier and leading column of the primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective bounds that make the row a dated record; part of the PK.
• BUSINESS_GROUP_ID — business group context and PK member.
• CLIENT_ID — multi-tenancy discriminator; PK member.
• ELEMENT_TYPE_ID — the element being linked.
• PAYROLL_ID — target payroll for the link.
• COST_ALLOCATION_KEYFLEX_ID — foreign key to PAY_COST_ALLOCATION_KEYFLEX, defining cost allocation.
• PAY_BASIS_ID — foreign key to PER_PAY_BASES.
• ORGANIZATION_ID — organizational scope of the link.
• LOCATION_ID — location scope of the link.
• LINK_TO_ALL_PAYROLLS_FLAG — indicates whether the element applies to every payroll.
• STATUS — active/inactive state of the link.
• LAST_UPD_DATE — audit timestamp for the last modification.

ELEMENT_LINK_ID is the surrogate primary key; BUSINESS_GROUP_ID, ELEMENT_TYPE_ID, and the effective dates serve as business-key candidates that together with PAYROLL_ID, ORGANIZATION_ID, or LOCATION_ID uniquely describe the link's scope.

Common Use Cases and Queries

Typical scenarios involve checking which elements can be processed for a given payroll or organization, auditing cost allocation setups, and diagnosing element entry rejections caused by missing or expired links. A frequent pattern joins element links to payroll and element definitions:

• List all active links for a payroll: filter by PAYROLL_ID and STATUS, and test the effective dates against the current date.
• Trace cost allocation: join COST_ALLOCATION_KEYFLEX_ID to PAY_COST_ALLOCATION_KEYFLEX.
• Validate pay basis: join PAY_BASIS_ID to PER_PAY_BASES.

A representative query pattern is:

SELECT el.ELEMENT_LINK_ID, el.ELEMENT_TYPE_ID, el.PAYROLL_ID, el.STATUS
FROM HR.HR_H2PI_ELEMENT_LINKS el
WHERE el.BUSINESS_GROUP_ID = :p_bg_id
AND TRUNC(SYSDATE) BETWEEN el.EFFECTIVE_START_DATE AND NVL(el.EFFECTIVE_END_DATE, TRUNC(SYSDATE));

Related Objects

The FK metadata documents direct dependencies on two reference tables, and additional related objects complete the payroll element context:

• PAY_COST_ALLOCATION_KEYFLEX — joined via COST_ALLOCATION_KEYFLEX_ID.
• PER_PAY_BASES — joined via PAY_BASIS_ID.
• PAY_ELEMENT_TYPES_F — element metadata referenced through ELEMENT_TYPE_ID.
• PAY_PAYROLLS_F — payroll definitions referenced through PAYROLL_ID.
• HR_ALL_ORGANIZATION_UNITS and HR_LOCATIONS_ALL — organizational and location scope via ORGANIZATION_ID and LOCATION_ID.
• PAY_ELEMENT_ENTRIES_F — the downstream entries governed by these links.

Together these objects support element eligibility, processing, and cost distribution across the payroll cycle.