Search Results per_cagr_entitlement_lines_f




Overview
Explore in AI Chat

Overview

PER_CAGR_ENTITLEMENT_LINES_F is a date-tracked (the _F suffix denotes a date-effective or "frozen" datetrack table) table in the HR schema of Oracle E-Business Suite, belonging to the PER (Human Resources) product family. It stores the individual entitlement lines that make up a Collective Agreement (CAGR) entitlement rule, which governs how leave, absence, or accrual entitlements are calculated and granted for a workforce segment. Each row represents a single entitlement line, capturing the value, range, grade/step spine, and eligibility profile that determine how much entitlement a worker receives.

The table carries the standard EBS date-effective pattern, using EFFECTIVE_START_DATE and EFFECTIVE_END_DATE alongside the surrogate primary key CAGR_ENTITLEMENT_LINE_ID to preserve historical versions of each line. From a Data Vault modeling perspective, this object is best classified as a link, because it resolves many-to-many associations between entitlements and the spine/step structures. This classification is a heuristic suggested by the foreign key structure rather than an Oracle-defined designation.

Key Information Stored

The most operationally significant columns are:

  • CAGR_ENTITLEMENT_LINE_ID — Surrogate primary key; uniquely identifies each entitlement line record.
  • CAGR_ENTITLEMENT_ID — Foreign key to PER_CAGR_ENTITLEMENTS; identifies the parent entitlement rule this line belongs to.
  • ELIGY_PRFL_ID — Links the line to the eligibility profile that determines which workers qualify.
  • PARENT_SPINE_ID — Foreign key to PER_PARENT_SPINES; ties the line to the parent spine structure.
  • GRADE_SPINE_ID — Identifies the grade spine governing the entitlement calculation.
  • STEP_ID, FROM_STEP_ID, TO_STEP_ID — Define the step range to which the line applies, enabling step-based entitlement tiers.
  • MANDATORY — Flag indicating whether the entitlement line is compulsory.
  • VALUE — The entitlement quantity or value granted by the line.
  • RANGE_FROM, RANGE_TO — Numeric boundaries that bound the line's applicability.
  • STATUS — Lifecycle status of the line record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective window defining when the line version is valid; both participate in the primary key.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, OBJECT_VERSION_NUMBER — Standard audit and concurrency columns.

The documented unique index PER_CAGR_ENTITLEMENT_LINES_PK spans CAGR_ENTITLEMENT_LINE_ID, EFFECTIVE_END_DATE, and EFFECTIVE_START_DATE, making the effective dates business-key candidates for date-effective lookup.

Common Use Cases and Queries

Typical scenarios include auditing how entitlement amounts vary by grade and step, reconciling entitlements to eligibility profiles, and reporting effective-dated entitlement history. A common query pattern joins the line to its parent entitlement and spine structures, filtered by effective date:

  • Retrieve current entitlement lines for a given entitlement: SELECT * FROM per_cagr_entitlement_lines_f WHERE cagr_entitlement_id = :id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Report entitlement values by grade and step: join GRADE_SPINE_ID and STEP_ID against spine tables, grouping by VALUE.
  • Reconcile eligibility: join on ELIGY_PRFL_ID to PER_ELIGIBILITY_PROFILES to identify the population each line serves.
  • Historical audit: query without a date filter to see all date-effective versions keyed by CAGR_ENTITLEMENT_LINE_ID.

Related Objects

  • PER_CAGR_ENTITLEMENTS — Parent table joined on CAGR_ENTITLEMENT_ID.
  • PER_PARENT_SPINES — Referenced via PARENT_SPINE_ID.
  • PER_ELIGIBILITY_PROFILES — Referenced via ELIGY_PRFL_ID for eligibility resolution.
  • PER_GRADE_SPINES / PER_SPINES — Provide grade and step context via GRADE_SPINE_ID and STEP_ID.
  • PER_CAGR_ENTITLEMENT_LINES — The non-datetracked view frequently exposed for reporting.
  • PER_ENTITLEMENT_LINES — Conceptual counterpart in related entitlement processing.