Results for “prtts_num”

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

Overview

BEN_PL_R_OIPL_PREM_BY_MO_D is an APPS-owned reporting view within the Oracle Advanced Benefits (BEN) module. Its name follows the standard EBS convention for a non-industry-specific (non-OIPL) reporting layer: the "_D" suffix indicates a denormalized, display-ready projection of a "_F" base table, in this case BEN_PL_R_OIPL_PREM_BY_MO_F. The view presents premium amounts broken down by month number (MO_NUM) and year number (YR_NUM) for a given plan/option/actual premium combination, joining in lookup meanings, currency names, and user audit information so that the data is immediately consumable by reports, concurrent programs, and integrations without requiring the caller to resolve foreign keys manually.

The view is registered in the ETRM as VALID and is defined entirely within the APPS schema, meaning it is queried through APPS credentials or a synonym pointing to APPS. It is exposed as a read-only object; no DML should be directed at it.

Underlying Base Objects

The view is defined over the following documented referenced objects:

• BEN_PL_R_OIPL_PREM_BY_MO_F — the primary base table (referenced via synonym). It holds the premium-by-month rows and supplies the row identity, effective dating, MO_NUM, YR_NUM, VAL, PRTTS_NUM, MNL_ADJ_FLAG, UOM, and audit columns.
• BEN_ACTL_PREM_F — joined via ACTL_PREM_ID to supply the actual premium name (ACTL_PREM_NAME).
• FND_CURRENCIES_TL — joined via UOM (currency code) to resolve the currency NAME (UOM_NAME).
• FND_USER_VIEW — joined via LAST_UPDATED_BY to expose the USER_NAME of the last updating user.
• HR_LOOKUPS — joined via MNL_ADJ_FLAG against lookup type 'YES_NO' to translate the flag into MNL_ADJ_MEANING.
• HR_API — referenced package supporting date-effective/HR utility logic associated with the object family.

All non-primary joins are implemented as outer joins (the "(+)" operator), so a row in the base table is retained even when no matching premium, currency, user, or lookup value exists.

Key Columns

• ROW_ID — the base table rowid, useful for row-level identification.
• PL_R_OIPL_PREM_BY_MO_ID — primary key of the underlying premium-by-month record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective bounds for the row; queries for "current" data must filter on SYSDATE within these bounds.
• MO_NUM — the month number to which the premium value applies. Because it is an unconstrained numeric column, filtering by month is the most common access path.
• YR_NUM — the year number associated with MO_NUM.
• VAL — the premium amount or value for the month/year.
• UOM_NAME — resolved currency name from FND_CURRENCIES_TL (the UOM column stores the currency code).
• ACTL_PREM_NAME — descriptive name of the actual premium.
• MNL_ADJ_MEANING — Yes/No meaning of the manual adjustment flag.
• PRTTS_NUM — participant number associated with the premium record.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns, with USER_NAME resolving the updating user.

Common Use Cases and Queries

Typical uses include benefit premium cost reporting, audit of premium adjustments by month, and extracting monthly premium values for downstream payroll or actuarial feeds. Because the user searched for "mo_num", the most common access pattern filters on month number.

• Premiums for a specific month:

  SELECT mo_num, yr_num, val, uom_name, actl_prem_name
  FROM   apps.ben_pl_r_oipl_prem_by_mo_d
  WHERE  mo_num = :p_month
  AND    SYSDATE BETWEEN effective_start_date AND effective_end_date;

• Premium totals by month/year:

  SELECT mo_num, yr_num, SUM(val)
  FROM   apps.ben_pl_r_oipl_prem_by_mo_d
  WHERE  SYSDATE BETWEEN effective_start_date AND effective_end_date
  GROUP BY mo_num, yr_num
  ORDER BY yr_num, mo_num;

• Audit of recent changes:

  SELECT pl_r_oipl_prem_by_mo_id, last_update_date, user_name
  FROM   apps.ben_pl_r_oipl_prem_by_mo_d
  WHERE  last_update_date >= SYSDATE - 30;

Callers should always include effective-date predicates when current data is required, and should treat the view as read-only.