Search Results ben_prtt_prem_by_mo_f




Overview
Explore in AI Chat

Overview

BEN_PRTT_PREM_BY_MO_F is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It stores the premium owed to a plan carrier on a monthly basis per participant, forming the participant-level, month-by-month breakdown of premium obligations. This tabular data is central to benefits billing, carrier reconciliation, and payroll deduction verification, because it isolates the recurring monthly premium amount attributed to each enrolled participant rather than aggregating at the plan or program level.

The _F suffix denotes a date-tracked (datetrack) table governed by effective start and end dates, which is the standard Oracle EBS pattern for maintaining historical versions of a business entity. Per the heuristic Data Vault classification supplied in the metadata, this object is modeled as standalone; in Data Vault terms this suggests it behaves most like a satellite carrying descriptive premium measures, because its key is composed of a surrogate identifier plus effective dating rather than a pure business hub key. The modeling suggestion is that the surrogate identity plus effective dating provides the row-level grain, while the premium values are the descriptive payload.

Key Information Stored

The table carries 57 documented columns. The most significant include:

The unique index BEN_PRTT_PREM_BY_MO_PK on (PRTT_PREM_BY_MO_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is documented as the primary key; the metadata does not identify an additional business-key unique index, so PRTT_PREM_BY_MO_ID functions as the practical surrogate identity within effective-dated versions.

Common Use Cases and Queries

Typical use cases center on monthly premium reporting, carrier invoice reconciliation, and audit of manual adjustments. A representative query retrieves the current monthly premium for a participant:

  • SELECT mo_num, yr_num, val, cr_val, uom FROM ben.ben_prtt_prem_by_mo_f WHERE prtt_prem_id = :p_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
  • Summarize premiums per month: SELECT yr_num, mo_num, SUM(val) FROM ben.ben_prtt_prem_by_mo_f WHERE effective_end_date = '31-DEC-4712' GROUP BY yr_num, mo_num;
  • Flag manually adjusted rows: SELECT prtt_prem_by_mo_id, val FROM ben.ben_prtt_prem_by_mo_f WHERE mnl_adj_flag = 'Y' AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
  • Trace the concurrent process that produced a row via REQUEST_ID and PROGRAM_ID for reconciliation.

Related Objects

  • PAY_COST_ALLOCATION_KEYFLEX — referenced by COST_ALLOCATION_KEYFLEX_ID on this table; join on the keyflex ID to resolve cost allocation segments.
  • BEN_PRTT_PREM_F — parent participant premium header, joined via PRTT_PREM_ID.
  • BEN_PRTT_PREM_BY_MO_PK — the unique index enforcing the composite primary key.
  • BEN_PRTT_ENRT_RSLT_F — participant enrollment results that ultimately drive premium derivation.
  • BEN_PL_BEN_PREM_F and BEN_PL_PREM_F — plan-level premium definitions providing the rate basis.
  • HR_ORGANIZATION_UNITS — the business group referenced by BUSINESS_GROUP_ID.