Search Results ben_acty_base_rt_ctfn_f




Overview
Explore in AI Chat

Overview

The BEN_ACTY_BASE_RT_CTFN_F table is a core configuration object within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, present in both the 12.1.1 and 12.2.2 releases. It is owned by the BEN schema and carries a status of VALID. As documented in the ETRM reference, its purpose is to define the Certifications required for Rates. In practice, this table stores the association between a benefit rate (an activity base rate) and the certifications that a participant must satisfy in order for that rate to apply. It therefore governs eligibility conditions attached to rate calculations within a benefits plan.

The object is a date-tracked (effective-dated) table, as indicated by the presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns and the "_F" suffix that follows Oracle's standard naming convention for date-effective entities. The heuristic Data Vault classification mined from its foreign-key structure is standalone, which suggests that the table functions as an independent detail or reference entity rather than an explicit hub, link, or satellite in a formal Data Vault model. Analysts modeling this table for warehouse purposes should treat it as a standalone, date-versioned configuration record.

Key Information Stored

The table contains 45 documented columns. The most significant include the following:

The documented unique index, BEN_ACTY_BASE_RT_CTFN_F_PK, is defined on the business-key candidate set of ACTY_BASE_RT_CTFN_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, distinguishing the surrogate identifier from the date-effective uniqueness constraint.

Common Use Cases and Queries

Typical uses center on retrieving which certifications apply to a given rate, validating configuration during benefits setup, and reporting on rate eligibility rules. A query to surface currently effective certification requirements for a specific rate might appear as follows:

  • SELECT ctfn.ACTY_BASE_RT_CTFN_ID, ctfn.ENRT_CTFN_TYP_CD, ctfn.RQD_FLAG, ctfn.CTFN_RQD_WHEN_RL FROM ben.BEN_ACTY_BASE_RT_CTFN_F ctfn WHERE ctfn.ACTY_BASE_RT_ID = :rate_id AND TRUNC(SYSDATE) BETWEEN ctfn.EFFECTIVE_START_DATE AND ctfn.EFFECTIVE_END_DATE;

Because the table includes thirty descriptive flexfield attributes, reporting solutions frequently expose ABC_ATTRIBUTE_CATEGORY and selected ABC_ATTRIBUTE columns to accommodate client-specific certification metadata. Data extraction routines should always apply the effective-date predicate to avoid returning superseded configuration rows.

Related Objects

The table participates in the broader Advanced Benefits rate and certification configuration model. Significant related objects include the following:

  • BEN_ACTY_BASE_RT_F — the activity base rate header, joined on ACTY_BASE_RT_ID.
  • BEN_RATE_F and its child tables — the rates to which certifications are attached.
  • BEN_CTFN_DFNTN_F / certification definition tables — define the certification types referenced by ENRT_CTFN_TYP_CD.
  • BEN_PL_RT_CTFN_F — plan-level rate certification configuration where applicable.
  • BEN_ACTY_RT_CTFN_F — related certification-rate association tables within the same family.
  • FND_FLEX_VALUES and FND_FLEX_VALUE_SETS — resolve the descriptive flexfield and code values used in the ABC_ATTRIBUTE and type-code columns.

Consumers integrating this data should rely on the effective-date keys and ACTY_BASE_RT_ID to establish joins with the rate and certification definition entities described above.