Results for “ben_prtt_rt_val_ctfn_prvdd”

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

Overview

The BEN_PRTT_RT_VAL_CTFN_PRVDD table resides in the BEN schema and belongs to the Oracle Advanced Benefits (BEN) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description — Certification provided for participation — indicates that the table stores the certification records captured against a participant's rate or eligibility value when the plan configuration requires the participant to provide proof of certification (for example, evidence of student status, disability, or dependency) in order to qualify for and retain a specific rate value.

The table is classified (heuristically, mined from its foreign key structure) as standalone in the Data Vault model. It is therefore best modeled as an independent table rather than being folded into an existing hub or link. Its sole documented foreign key points to BEN_PRTT_RT_VAL, meaning each certification record is anchored to the participant rate value it certifies. The table carries 48 documented columns in the ETRM 12.2.2 schema.

Key Information Stored

The physical structure centres on a single-column surrogate primary key and a compact set of business attributes, supplemented by a wide generic attribute block and standard WHO audit columns.

• PRTT_RT_VAL_CTFN_PRVDD_ID — the surrogate primary key, enforced by the unique index BEN_PRV_CTFN_PRVDD_PK. This is the only documented unique/business-key candidate.
• PRTT_RT_VAL_ID — the foreign key to BEN_PRTT_RT_VAL, tying the certification to a specific participant rate value.
• ENRT_CTFN_RQD_FLAG — flags whether certification is required for the enrolment/rate value.
• ENRT_CTFN_TYP_CD — the certification type code (e.g., student, disability).
• ENRT_CTFN_RECD_DT and ENRT_CTFN_DND_DT — the date the certification was received and its expiry/due date.
• BUSINESS_GROUP_ID — the legislative/HR business group that owns the record, essential for multi-org data segregation.
• RVC_ATTRIBUTE_CATEGORY and RVC_ATTRIBUTE1 through RVC_ATTRIBUTE30 — a descriptive flexfield (DFF) block for capturing client-specific certification detail.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard WHO audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context for the process that last touched the row.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J framework.

Common Use Cases and Queries

Typical usage centres on open enrolment validation, rate certification tracking, and audit reporting. A benefits administrator or report developer would query this table to list outstanding or expired certifications for a participant.

• Identify certifications that have expired but are still attached to an active rate value:

  SELECT c.PRTT_RT_VAL_ID, c.ENRT_CTFN_TYP_CD, c.ENRT_CTFN_DND_DT
  FROM   BEN_PRTT_RT_VAL_CTFN_PRVDD c
  WHERE  c.ENRT_CTFN_DND_DT < SYSDATE;

• Join to BEN_PRTT_RT_VAL to trace a certification back to its participant and plan:

  SELECT r.PRTT_RT_VAL_ID, c.ENRT_CTFN_TYP_CD, c.ENRT_CTFN_RECD_DT
  FROM   BEN_PRTT_RT_VAL_CTFN_PRVDD c,
         BEN_PRTT_RT_VAL r
  WHERE  c.PRTT_RT_VAL_ID = r.PRTT_RT_VAL_ID;

• Filter by BUSINESS_GROUP_ID for multi-org reporting, and expose the DFF columns as report attributes.

Related Objects

The following objects are most relevant, based on the documented FK relationship and the Advanced Benefits data model:

• BEN_PRTT_RT_VAL — the parent table referenced via PRTT_RT_VAL_ID; the certified participant rate value.
• BEN_PRTT_RT_VAL_CTFN — the certification definition table that supplies ENRT_CTFN_TYP_CD values.
• BEN_PRTT_ENRT_RSLT — participant enrolment results tied to the certified rate value.
• BEN_PRTT_CTFN_PRVDD — participant-level certification records.
• FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — backing the DFF and code lookups.
• FND_OBJECTS / concurrent program definitions — resolving PROGRAM_APPLICATION_ID and PROGRAM_ID.