Search Results ben_acty_rt_ptd_lmt_f




Overview
Explore in AI Chat

Overview

BEN_ACTY_RT_PTD_LMT_F is a table within the BEN (Advanced Benefits) schema of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. Its stated purpose is to store period-to-date limits applied to activity rates. In the Oracle Advanced Benefits (OAB) calculation engine, activity rates drive cost and credit computations for benefits plans; the period-to-date limit records introduced in this table constrain the cumulative amount that may be applied to a given activity base rate within a defined period, thereby enforcing plan rules such as annual or quarterly ceilings on imputed income, credits, or contributions.

The object follows the standard EBS date-tracked ( _F ) design pattern, carrying EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns in its primary key alongside the surrogate identifier ACTY_RT_PTD_LMT_ID. This enables date-effective, auditable versioning of each limit definition without destroying prior configurations. The table is held in the BEN schema and is flagged as Oracle proprietary and confidential.

On the heuristic Data Vault classification supplied in the metadata, the object is modeled as standalone, meaning no foreign-key relationships were mined from its constraint structure. From a data-vaulting perspective this suggests treating the table as an independent hub-like entity keyed on its own surrogate identifier, with the date-effectivity attributes acting as descriptive satellite context rather than participating in a modelled link to another hub. The classification is a modeling suggestion only; the operational semantics still relate the limit to an activity base rate and to a period-to-date limit definition.

Key Information Stored

The documented physical schema defines 43 columns. The following are the most important from an implementation and reporting standpoint:

  • ACTY_RT_PTD_LMT_ID — Surrogate primary key for the limit record; the first component of the composite key.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effectivity columns; together with the surrogate identifier they form the unique business key BEN_ACTY_RT_PTD_LMT_F_PK.
  • BUSINESS_GROUP_ID — Multi-tenancy discriminator identifying the enterprise or business group that owns the configuration row.
  • ACTY_BASE_RT_ID — Reference to the activity base rate to which this period-to-date limit is applied.
  • PTD_LMT_ID — Reference to the period-to-date limit definition that supplies the limiting rule.
  • APL_ATTRIBUTE_CATEGORY and APL_ATTRIBUTE1 through APL_ATTRIBUTE30 — The standard descriptive flexfield (DFF) segment set, providing 30 attribute slots plus a context category for customer-specific extensions without schema modification.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard EBS audit columns recording creation and last-modification identity and timestamps.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAB middle tier to detect concurrent updates.

The unique index BEN_ACTY_RT_PTD_LMT_F_PK (ACTY_RT_PTD_LMT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the documented business-key candidate and should be treated as the authoritative uniqueness constraint for any interface or data-load routine.

Common Use Cases and Queries

Typical uses include auditing the limit configuration attached to a rate, reconciling date-effective changes across plan years, and extracting DFF setup for migration between environments.

Retrieve currently effective limits for a business group:

SELECT acty_rt_ptd_lmt_id, acty_base_rt_id, ptd_lmt_id,
       effective_start_date, effective_end_date
FROM   ben_acty_rt_ptd_lmt_f
WHERE  business_group_id = :p_bg_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Trace the full version history of a single limit record:

SELECT acty_rt_ptd_lmt_id, effective_start_date, effective_end_date,
       last_update_date, last_updated_by
FROM   ben_acty_rt_ptd_lmt_f
WHERE  acty_rt_ptd_lmt_id = :p_id
ORDER BY effective_start_date;

Because ACTY_BASE_RT_ID and PTD_LMT_ID are not enforced by documented foreign keys, joins to the parent base-rate and limit tables must be written defensively and validated against ETRM relationship data before deployment.

Related Objects

The metadata documents no foreign keys, so the following relationships are inferred from the column semantics and the wider BEN schema:

  • BEN_ACTY_BASE_RT_F — joined on ACTY_BASE_RT_ID (and overlapping effective dates) to resolve the activity base rate being limited.
  • BEN_PTd_LMT_F — joined on PTD_LMT_ID to retrieve the period-to-date limit definition and its threshold values.
  • BEN_ACTY_RT_PTD_LMT_F_PK — the unique index enforcing the composite key on ACTY_RT_PTD_LMT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.
  • FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — the standard flexfield value tables underpinning the APL_ATTRIBUTE descriptive flexfield segments.
  • BEN_BUSINESS_GROUP_ID references (HR_OPERATING_UNITS) — the owning business group resolved through BUSINESS_GROUP_ID.

Because the table is classified as standalone with no mined FK structure, any integration or extract should treat referential integrity as application-enforced rather than database-enforced, and should include explicit existence checks against the referenced base-rate and limit tables.