Search Results mnl_adj_flag




Overview
Explore in AI Chat

Overview

The table BEN.BEN_PL_R_OIPL_PREM_BY_MO_F is a core Advanced Benefits (BEN) object in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the total premium owed to a plan, or to an option within a plan, for a plan sponsor on a per-month basis. The name decomposes as Benefit (BEN) Plan (PL) Report (R) / OIPL (Option In Plan) Premium by Month (PREM_BY_MO), with the _F suffix confirming it as a date-tracked (effectively dated) table. The object is a standalone table, not a subtype of a shared entity family, and it carries the standard 53-column physical layout documented in ETRM 12.2.2.

From a Data Vault modeling perspective, the heuristic classification mined from the FK structure is standalone. This suggests modeling the table as a self-contained structure rather than as a hub, link, or satellite within a broader Data Vault design. Its role is primarily analytical and reporting-oriented: it aggregates premium amounts by month for plan sponsors, making it a source for premium reconciliation, carrier billing, and financial reporting within Advanced Benefits.

Key Information Stored

The 53 documented columns fall into three functional groups: key columns, descriptive/attribute columns, and standard EBS audit columns. The most important are:

Common Use Cases and Queries

Typical uses include reconciling total premium owed per plan or option per month, generating carrier invoice support, and producing plan-sponsor cost-allocation reports. A representative point-in-time query retrieves the premium value for a given ID and effective date:

  • SELECT pl_r_oipl_prem_by_mo_id, mo_num, yr_num, val, uom FROM ben.ben_pl_r_oipl_prem_by_mo_f WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
  • Filtering by mo_num and yr_num to build monthly premium trend reports across plan sponsors.
  • Joining COST_ALLOCATION_KEYFLEX_ID to PAY_COST_ALLOCATION_KEYFLEX to present allocated premium by cost center.
  • Using ACTL_PREM_ID and MNL_ADJ_FLAG to separate system-calculated premiums from manual adjustments for audit.

Related Objects

The documented FK relationship and surrounding BEN/PAY objects include:

  • PAY_COST_ALLOCATION_KEYFLEX — joined via COST_ALLOCATION_KEYFLEX_ID.
  • BEN_PL_R_OIPL_PREM_BY_MO_PK — the primary key / unique index over PL_R_OIPL_PREM_BY_MO_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE.
  • BEN_ACTL_PREM_F — the actual premium source referenced by ACTL_PREM_ID.
  • BEN_PL_F (Benefit Plan) and BEN_OIPL_F (Option in Plan) — plan and option context for the monthly premium.
  • BEN_PER_PL_F and BEN_PER_PL_OPT_F — person/plan participation records used to allocate premium.
  • FND_REQUEST / FND_CONCURRENT_REQUESTS — joined via REQUEST_ID for the program that populated the record.