Search Results ben_acty_vrbl_rt




Overview
Explore in AI Chat

Overview

BEN_ACTY_VRBL_RT is an APPS-owned database view within the Oracle Advanced Benefits (BEN) module. In Oracle EBS 12.1.1 and 12.2.2, it exposes the "retrofitted" (date-effective) representation of activity variable rate records — the ordered association between an activity base rate and a variable rate profile. The object carries an ETRM status of VALID and is marked in the metadata as "Retrofitted," which indicates that it presents the row for the current effective date rather than the entire history held in the underlying table. Its primary role is to give reports, concurrent programs, Fast Formulas, and integration interfaces a single current-version row set without requiring callers to write their own effective-date (datetrack) predicates.

The view is defined at the APPS schema level and joins to FND_SESSIONS to resolve the effective date of the active user session, so the rows returned always reflect the business date applicable to the running session.

Underlying Base Objects

The view is defined over two documented referenced objects, both accessed through APPS synonyms:

  • BEN_ACTY_VRBL_RT_F — the date-effective ( "_F" ) base table that stores all versioned rows of activity variable rate definitions. The view selects from this table using the alias AVR.
  • FND_SESSIONS — the session table that supplies the effective date. The view correlates the session row to the caller through USERENV('SESSIONID'), the standard EBS mechanism for identifying the current Forms or OAF session.

The retrofit predicate is expressed as two correlated scalar subqueries: rows are returned only where EFFECTIVE_START_DATE is less than or equal to the session effective date and EFFECTIVE_END_DATE is greater than or equal to that same date. This is the classic EBS nondatetrack-retrofit pattern, and it means the view behaves as a "current row only" projection of the underlying "_F" table. No joins to descriptive or lookup tables are present in the view text; lookups must be resolved by the caller if display values are required.

Key Columns

Common Use Cases and Queries

Typical uses include ad hoc reporting on variable rate setup, reconciliation between base rates and variable rate profiles, extracts feeding payroll or benefits calculation validation, and diagnostic queries during implementation. Because the view already filters on the session effective date, queries are simple and should not repeat datetrack predicates.

To list current variable rate entries for a business group:

  • SELECT acty_vrbl_rt_id, acty_base_rt_id, vrbl_rt_prfl_id, ordr_num, effective_start_date, effective_end_date FROM apps.ben_acty_vrbl_rt WHERE business_group_id = :p_bg_id ORDER BY acty_base_rt_id, ordr_num;

To check ordering conflicts for a base rate:

  • SELECT acty_base_rt_id, ordr_num, COUNT(*) FROM apps.ben_acty_vrbl_rt GROUP BY acty_base_rt_id, ordr_num HAVING COUNT(*) > 1;

To inspect flexfield data for a specific entry:

  • SELECT acty_vrbl_rt_id, avr_attribute_category, avr_attribute1, avr_attribute2 FROM apps.ben_acty_vrbl_rt WHERE acty_vrbl_rt_id = :p_id;

Note that inserts and updates must be performed against BEN_ACTY_VRBL_RT_F through the supported Advanced Benefits APIs, not against this view, since the view carries no Instead-Of trigger and exists purely for read access.