Search Results ben_asnt_set_rt_f




Overview
Explore in AI Chat

Overview

BEN_ASNT_SET_RT_F is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, documented as valid in release 12.1.1 and 12.2.2. The ETRM metadata describes it as the "Rate applied to an assignment set," meaning each row associates a compensation or rate value with a specific assignment set as used by the Advanced Benefits rate engine. In EBS Advanced Benefits, assignment sets define populations of participants (assignments) that share eligibility or rate criteria; this table captures the rate information that is then applied across that population.

The table is date-tracked: it carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns and is defined with 45 documented columns. The primary key, BEN_ASNT_SET_RT_F_PK, is composed of ASNT_SET_RT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, confirming the standard EBS effective-dating pattern where a surrogate identifier (ASNT_SET_RT_ID) is combined with the date range to guarantee uniqueness over time.

From a Data Vault modeling perspective, the ETRM heuristic classifies this object as standalone: it does not participate in a mined foreign-key relationship to another table within the documented metadata. Modelers may therefore treat it as a standalone satellite-like construct keyed by its own business identifier, rather than as a hub or link driven by external references. Because the FK data was not mined, the classification should be regarded as a modeling suggestion rather than a definitive relationship map.

Key Information Stored

Among the 45 documented columns, the following are the most significant for understanding and querying this object:

  • ASNT_SET_RT_ID — the surrogate identifier for the rate record and the first component of the primary key.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity range completing the primary key.
  • ASSIGNMENT_SET_ID — identifies the assignment set to which the rate applies, linking the rate definition to a participant population.
  • VRBL_RT_PRFL_ID — reference to the variable rate profile used to derive the applied rate.
  • EXCLD_FLAG — indicates whether the associated assignment set represents an exclusion (negative) population.
  • ORDR_NUM — ordering sequence used when multiple rates apply within a set.
  • BUSINESS_GROUP_ID — the operating business group (HR security) context for the record.
  • ASR_ATTRIBUTE_CATEGORY and ASR_ATTRIBUTE1–30 — the descriptive flexfield (DFF) segment columns, providing extensible, customer-defined attributes on the rate record.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS WHO-audit columns.
  • OBJECT_VERSION_NUMBER — the optimistic locking version column used by the Oracle Applications framework.

The primary key (ASNT_SET_RT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the only unique index documented and is both the surrogate-key carrier and the business-key candidate per the metadata.

Common Use Cases and Queries

Typical uses involve reporting the rates applied to assignment sets, auditing rate changes over time, and integrating with rate calculation or payroll interfaces. Because the table is date-tracked, queries must constrain the effective date.

  • Retrieve the current rate for a given rate record: 
    SELECT asnt_set_rt_id, assignment_set_id, vrbl_rt_prfl_id,
       ordr_num, exld_flag
FROM   ben_asnt_set_rt_f
WHERE  asnt_set_rt_id = :p_id
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date;
  • List all rates linked to a single assignment set: 
    SELECT asnt_set_rt_id, effective_start_date, effective_end_date
FROM   ben_asnt_set_rt_f
WHERE  assignment_set_id = :p_assignment_set_id
ORDER  BY effective_start_date;
  • Historical ("as-of") rate analysis by supplying an effective date value.
  • Extract DFF attributes (ASR_ATTRIBUTE1–30) for reporting or downstream system feeds.

Related Objects

Although the metadata describes this object as standalone, in practice it operates within the BEN rate infrastructure. The most significant related objects include:

Because no FK relationships were mined, join keys above should be verified against the target instance before use in production SQL.