Search Results pay_assignment_link_usages_pk

Overview

HR.PAY_ASSIGNMENT_LINK_USAGES_F is a DateTracked intersection table that models the relationship between element links (PAY_ELEMENT_LINKS_F) and assignments (PER_ALL_ASSIGNMENTS_F). Its purpose is performance-oriented: rather than repeatedly evaluating eligibility flexfield criteria at runtime, the table denormalizes derived information so the payroll engine can rapidly determine whether a given assignment is eligible for a specific element link. In the current documented implementation, only the PEOPLE_GROUP_ID flexfield eligibility criterion is denormalized into this structure.

The object resides in the HR schema (product design data reference PAY.PAY_ASSIGNMENT_LINK_USAGES_F) and is stored in the APPS_TS_TX_DATA tablespace with PCT Free 10. From a Data Vault modeling perspective, the metadata classifies this object heuristically as a standalone structure. Where link-style modeling is applied, the table functions as a link between the assignment and element link hubs, while the effective-dated columns and surrogate identifier support satellite-style historization of eligibility over time.

Key Information Stored

The table stores five documented columns. Distinguishing the surrogate key from the business-key candidates is essential for correct querying:

• ASSIGNMENT_LINK_USAGE_ID (NUMBER(15)) — System-generated primary key column; the surrogate identifier for each eligibility row.
• EFFECTIVE_START_DATE (DATE) — Effective start date of the eligibility record; participates in the primary key and in DateTrack filtering.
• EFFECTIVE_END_DATE (DATE) — Effective end date of eligibility; the third primary key component and the DateTrack boundary.
• ELEMENT_LINK_ID (NUMBER) — Foreign key to PAY_ELEMENT_LINKS_F, identifying the element link whose eligibility is being tracked.
• ASSIGNMENT_ID (NUMBER(10)) — Foreign key to PER_ALL_ASSIGNMENTS_F, identifying the assignment being evaluated.

The primary key PAY_ASSIGNMENT_LINK_USAGES_PK is defined on ASSIGNMENT_LINK_USAGE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. A second unique index, PAY_ASSIGNMENT_LINK_USAGES_U50, covers ASSIGNMENT_ID, ELEMENT_LINK_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, and represents the true business-key candidate: it guarantees that a given assignment-link pairing is effectively dated only once. Two nonunique indexes, PAY_ASSIGNMENT_LINK_USAGES_N1 (ELEMENT_LINK_ID) and PAY_ASSIGNMENT_LINK_USAGES_N2 (ASSIGNMENT_ID), support reverse lookups from either side of the relationship.

Common Use Cases and Queries

The principal use case is payroll eligibility resolution during element and assignment processing, where the application joins this table to PAY_ELEMENT_LINKS_F and PER_ALL_ASSIGNMENTS_F to avoid recomputing PEOPLE_GROUP_ID flexfield eligibility. Development and support teams also use it to troubleshoot why an element was or was not processed for a particular assignment.

A basic DateTracked projection uses the documented query text:

• SELECT ASSIGNMENT_LINK_USAGE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ELEMENT_LINK_ID, ASSIGNMENT_ID FROM HR.PAY_ASSIGNMENT_LINK_USAGES_F;

For point-in-time eligibility, constrain on both effective dates and assignment:

• ... WHERE ASSIGNMENT_ID = :p_assignment_id AND TRUNC(:p_date) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

For element-centric reporting, filter on ELEMENT_LINK_ID (served by index N1) and aggregate by assignment. Because the table is denormalized, correcting eligibility anomalies generally requires re-running the concurrent process that maintains it rather than performing direct DML, which preserves DateTrack integrity.

Related Objects

The table participates in the following significant relationships:

• PER_ALL_ASSIGNMENTS_F — joined via ASSIGNMENT_ID; the assignment side of the eligibility link.
• PAY_ELEMENT_LINKS_F — joined via ELEMENT_LINK_ID; the element link side of the eligibility link.
• PAY_ELEMENT_LINKS_F business key (ELEMENT_LINK_ID with effective dates) — used to resolve the link definition being evaluated.
• PER_PEOPLE_GROUPS — supplies the PEOPLE_GROUP_ID flexfield values denormalized into this table.
• PAY_PAYROLL_ACTIONS and PAY_ASSIGNMENT_ACTIONS — payroll processing paths that consume eligibility results.
• APPS schema synonyms — application code references HR.PAY_ASSIGNMENT_LINK_USAGES_F through APPS, as indicated by the documented dependency listing.

No database object references this table per the documented dependency information, confirming its role as a leaf-level denormalized store rather than a parent in the referential hierarchy.