Results for “ben_enrt_ctfn_f”

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

Overview

BEN_ENRT_CTFN_F is a transactional table in the BEN (Advanced Benefits) module of Oracle E-Business Suite, holding enrollment certification records. In Oracle Advanced Benefits, a certification is a formal attestation or supporting documentation that a participant must supply to satisfy the eligibility or pricing requirements of a benefit plan or option election. Typical examples include proof of good health (evidence of insurability), dependent status documentation, or proof of a life event. This table stores the definition and configuration of the certifications that can be attached to an enrollment, together with the descriptive flexfield segments that capture customer-specific attributes required at certification time.

The object is a date-tracked (datetrack-enabled) table, indicated by the presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE as part of the primary key. Because the table is non-multi-tenant and independently keyed, the ETRM heuristic Data Vault classification is standalone. As a modeling suggestion, the record behaves like a reference/definition satellite keyed by a numeric surrogate plus effective dating, rather than a hub or link.

Key Information Stored

The table contains 47 documented columns. The following are the most significant:

• ENRT_CTFN_ID – The surrogate primary key for an enrollment certification definition. It is the first component of BEN_ENRT_CTFN_F_PK.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – Datetrack columns that complete the primary key and define the validity window of each certification definition row.
• ENRT_CTFN_TYP_CD – Code identifying the certification type, distinguishing among the different category of certification that may be required.
• RQD_FLAG – Indicates whether the certification is required for the associated enrollment action.
• CTFN_RQD_WHEN_RL – Rule defining when the certification becomes mandatory.
• PL_ID, OIPL_ID, PLIP_ID – Foreign-key references to the benefit plan, plan option, and plan-in-program linkage to which the certification applies.
• BUSINESS_GROUP_ID – Identifies the business group that owns the certification configuration.
• ECF_ATTRIBUTE_CATEGORY and ECF_ATTRIBUTE1 through ECF_ATTRIBUTE30 – Descriptive flexfield segments used to capture customer-defined certification attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN – Standard audit (WHO) columns.
• OBJECT_VERSION_NUMBER – Optimistic locking column used by the application to detect concurrent updates.

Only BEN_ENRT_CTFN_F_PK, comprising ENRT_CTFN_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, is documented as a unique index and thus the business-key candidate; there is no separate business-key column set beyond the primary key.

Common Use Cases and Queries

Certifications are surfaced when validating whether a participant's election can proceed, when generating correspondence requesting proof, and when reporting outstanding or satisfied certifications. A representative query lists active certification definitions for a plan:

• Join to plan tables: SELECT c.ENRT_CTFN_ID, c.ENRT_CTFN_TYP_CD, c.RQD_FLAG FROM BEN_ENRT_CTFN_F c WHERE c.PL_ID = :plan_id AND SYSDATE BETWEEN c.EFFECTIVE_START_DATE AND c.EFFECTIVE_END_DATE;
• Reporting certifications requiring follow-up: filter on CTFN_RQD_WHEN_RL and RQD_FLAG for a given business group.
• Extract flexfield attributes for feeds: select the ECF_ATTRIBUTE_CATEGORY and numbered ECF_ATTRIBUTE columns for downstream integration.
• Datetrack corrections: query by effective dates to retrieve the correct historical definition.

Related Objects

Although the metadata classifies BEN_ENRT_CTFN_F as standalone, its PL_ID, OIPL_ID, and PLIP_ID columns imply logical relationships to program and plan-option configuration tables. The most significant related objects include:

• BEN_PL_F (benefit plan) – joined on PL_ID.
• BEN_OIPL_F (plan option) – joined on OIPL_ID.
• BEN_PLIP_F (plan-in-program linkage) – joined on PLIP_ID.
• BEN_ENRT_CTFN_F_PK – the primary key constraint used by dependent UI and APIs.
• Human Resources BUSINESS_GROUP_ID reference (HR_ALL_ORGANIZATION_UNITS).
• BEN APIs such as ben_enrollment and certification validation routines that read these records to enforce eligibility.