Search Results ben_acrs_ptip_cvg_f




Overview
Explore in AI Chat

Overview

BEN_ACRS_PTIP_CVG_F is a core configuration table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in both release 12.1.1 and 12.2.2. The table stores the definitions of coverages that span multiple plan types within a benefits program. The name decomposes into "ACRS" (Advanced Compensation and Related Setup) and "PTIP" (Plan Type In Program), indicating that the object defines how coverage rules, minimum and maximum allowed amounts, and associated program-level attributes apply across different plan type groupings. In ETRM terms, the table supports the configuration layer that drives plan eligibility and coverage limits presented to participants during enrollment and life event processing.

The documented schema carries 45 columns, with the primary key defined by BEN_ACRS_PTIP_CVG_PK on the composite of ACRS_PTIP_CVG_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This date-effective primary key is characteristic of Oracle HRMS/BEN datetracked configuration objects, ensuring that every coverage definition retains a valid-time history. The heuristic Data Vault classification mined from the FK structure is standalone. In Data Vault modeling terms, this suggests the table behaves more as a reference or standalone configuration entity rather than a strict hub, link, or satellite; however, its date-effective primary key and Business Group scoping mean it can equally be modeled as a satellite attached to a coverage hub where the natural key is ACRS_PTIP_CVG_ID.

Key Information Stored

The most operationally significant columns fall into identity, scoping, and limit categories:

  • ACRS_PTIP_CVG_ID — Surrogate identifier for the coverage definition; the first component of the primary key.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective range columns that complete the primary key and enforce valid-time versioning.
  • BUSINESS_GROUP_ID — Enterprise/legislative scoping key, segregating configuration between business groups.
  • NAME — User-facing name of the coverage across plan types.
  • MX_CVG_ALWD_AMT / MN_CVG_ALWD_AMT — Maximum and minimum coverage allowed amounts, the most important functional payload of the table.
  • PGM_ID — Foreign reference to the benefits program within which this coverage is defined.
  • APC_ATTRIBUTE_CATEGORY and APC_ATTRIBUTE1 through APC_ATTRIBUTE30 — Descriptive flexfield (DFF) columns providing the standard 30-segment extensibility Oracle reserves for client-specific coverage attributes.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/BC4J middle tier.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard WHO audit columns.

The unique index BEN_ACRS_PTIP_CVG_PK is the business-key candidate; no additional unique indexes are documented, so ACRS_PTIP_CVG_ID together with the effective dates is the only guaranteed-unique tuple.

Common Use Cases and Queries

Typical uses include validating maximum and minimum coverage limits per program during enrollment, auditing configuration changes over time, and reporting coverage structures across plan types. A common pattern retrieves the currently effective coverage rows for a program:

  • SELECT acrs_ptip_cvg_id, name, mn_cvg_alwd_amt, mx_cvg_alwd_amt FROM ben_acrs_ptip_cvg_f WHERE pgm_id = :pgm_id AND business_group_id = :bg_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Historical (as-of) query using a bind date instead of SYSDATE to reconstruct coverage limits at a prior point in time.
  • Joining to program and plan-type setup tables to flatten coverage-to-plan-type mappings for a BI Publisher or OBIEE report.
  • Audit queries filtering on LAST_UPDATE_DATE and LAST_UPDATED_BY to trace configuration drift.

Because the table is date-effective, all queries must supply an effective-date predicate to avoid returning multiple versions of the same ACRS_PTIP_CVG_ID.

Related Objects

The metadata classifies the object as standalone, meaning no outbound foreign keys are formally documented in the FK structure. Functionally, however, the following objects are the most significant collaborators:

These join paths should be validated against the live 12.2.2 schema, since ETRM does not document formal FK constraints for this table.