Results for “ben_elig_per_elctbl_chc_f”

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

Overview

BEN_ELCTBL_CHC_CTFN is a transactional table in the BEN (Advanced Benefits) schema of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It records the certifications required for an electable choice within a benefits election cycle. Each row links a specific electable choice (as represented through an eligibility-derived electable choice record) to the certification type that must be satisfied, either before or after enrollment is accepted. The table is central to plan-level or option-level certification enforcement, allowing HR administrators to require supporting documentation such as proof of dependent status, spousal coverage affidavits, or evidence of a qualifying life event before an election is confirmed.

Based on heuristic Data Vault classification mined from the foreign key structure, this object is satellite-leaning. This suggests that a warehouse model would treat BEN_ELCTBL_CHC_CTFN as a satellite attached to a business key drawn from the eligibility and enrollment hubs (BEN_ELIG_PER_ELCTBL_CHC and BEN_ENRT_BNFT), with descriptive attributes such as the certification type, required flag, and flexfield attributes carried on the satellite. This is a modeling suggestion only; the physical EBS schema does not enforce Data Vault semantics.

Key Information Stored

The table contains 49 documented columns. The most significant are:

• ELCTBL_CHC_CTFN_ID — the surrogate primary key, enforced by index BEN_ELCTBL_CHC_CTFN_PK. This is the identifier referenced by downstream tables and is the column users typically search for.
• ELIG_PER_ELCTBL_CHC_ID — foreign key to BEN_ELIG_PER_ELCTBL_CHC, tying the certification requirement to a specific electable choice derived from eligibility processing.
• ENRT_BNFT_ID — foreign key to BEN_ENRT_BNFT, linking the certification to an enrollment benefit record.
• ENRT_CTFN_TYP_CD — the certification type code, identifying which certification the participant must provide.
• RQD_FLAG — indicates whether the certification is mandatory for the electable choice.
• SUSP_IF_CTFN_NOT_PRVD_FLAG — controls whether the election is suspended when the certification is not provided.
• CTFN_DETERMINE_CD — the code governing how and when the certification requirement is determined.
• BUSINESS_GROUP_ID — the owning business group for multi-tenant security and reporting.
• ECC_ATTRIBUTE_CATEGORY and ECC_ATTRIBUTE1 through ECC_ATTRIBUTE30 — a descriptive flexfield block used to capture client-specific certification attributes.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and OBJECT_VERSION_NUMBER, which support concurrency control and change tracking.

The only documented unique index is BEN_ELCTBL_CHC_CTFN_PK on ELCTBL_CHC_CTFN_ID, so no additional business-key candidates are formally published, though combinations of ELIG_PER_ELCTBL_CHC_ID and ENRT_CTFN_TYP_CD frequently function as natural identifiers in practice.

Common Use Cases and Queries

Typical reporting scenarios include identifying which electable choices require certification, determining whether a certification was provided, and auditing suspended enrollments. A representative query joins to BEN_ELIG_PER_ELCTBL_CHC to resolve the choice against a participant:

• Query certifications required for a given electable choice: SELECT ctfn_id, enrt_ctfn_typ_cd, rqd_flag FROM ben_elctbl_chc_ctfn WHERE elig_per_elctbl_chc_id = :p_choice_id;
• Identify choices where suspension is enforced if certification is missing: SELECT * FROM ben_elctbl_chc_ctfn WHERE susp_if_ctfn_not_prvd_flag = 'Y';
• Join to BEN_ENRT_BNFT to reconcile certifications against enrollment benefits: SELECT c.elctbl_chc_ctfn_id, c.enrt_ctfn_typ_cd, b.enrt_bnft_id FROM ben_elctbl_chc_ctfn c, ben_enrt_bnft b WHERE c.enrt_bnft_id = b.enrt_bnft_id;
• Audit recent changes using OBJECT_VERSION_NUMBER and LAST_UPDATE_DATE for integration reconciliation.

Related Objects

• BEN_ELIG_PER_ELCTBL_CHC — parent electable choice; joined via ELIG_PER_ELCTBL_CHC_ID.
• BEN_ENRT_BNFT — enrollment benefit record; joined via ENRT_BNFT_ID.
• BEN_ELCTBL_CHC_CTFN_RBV — real-time business view referencing ELCTBL_CHC_CTFN_ID, used for display and validation.
• BEN_ELCTBL_CHC_CTFN_PK — the primary key unique index.
• BEN certification setup tables — supply ENRT_CTFN_TYP_CD values consumed by this table.