Results for “ben_acty_base_rt_ctfn_f_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• ACTY_BASE_RT_CTFN_ID — the surrogate identifier for the certification-rate relationship record. Together with the effective dates, it forms the primary key.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date-effective range governing when the certification requirement is active.
• ACTY_BASE_RT_ID — the foreign reference linking the record to the activity base rate against which the certification applies.
• ENRT_CTFN_TYP_CD — the enrollment or certification type code, identifying which certification is required.
• RQD_FLAG — a flag indicating whether the certification is required.
• CTFN_RQD_WHEN_RL — a rule or condition specifying when the certification becomes required.
• BUSINESS_GROUP_ID — the operating unit or business group context for the configuration row.
• The ABC_ATTRIBUTE1 through ABC_ATTRIBUTE30 columns (preceded by ABC_ATTRIBUTE_CATEGORY) provide a descriptive flexfield region for extensible, customer-defined attributes.
• Standard WHO and concurrency columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER — support auditing and optimistic locking.

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.