Search Results ben_pl_d




Overview
Explore in AI Chat

Overview

BEN_PL_D is an APPS-owned database view within the Oracle Advanced Benefits (BEN) module, documented in ETRM 12.2.2 and present in both EBS 12.1.1 and 12.2.2 environments. The suffix "_D" denotes a denormalized or descriptive view that flattens the plan-level data held in the BEN_PL_F entity into a single, query-friendly projection. Its status is VALID and its description is recorded simply as "Retrofitted," reflecting that the object was re-engineered from an earlier release into the current schema. The view exposes legislative and configuration attributes of a benefits plan — including eligibility, enrollment, proration, funding, and general-ledger posting controls — and serves primarily as a reporting and integration surface rather than a transactional object.

Underlying Base Objects

The view text is a straightforward projection over a single base object, the BEN_PL_F synonym, aliased as PLN. Because BEN_PL_F is date-effective, the view carries the complete effective-dating triad (PL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). No joins, unions, or aggregations are present in the definition; every column in BEN_PL_D maps one-to-one to a column in the underlying table. This makes BEN_PL_D a restrictive or convenience layer: it narrows the wide BEN_PL_F table to the columns most commonly required for plan-level reporting and downstream interfaces.

Key Columns

  • PL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — the effective-dated primary key identifying a plan version; consumers must apply date-range filters to retrieve the correct version.
  • NAME, SHORT_NAME, PL_CD, SHORT_CODE — descriptive and coded identifiers for the plan, used in reporting labels and cross-reference lookups.
  • POST_TO_GL_FLAG — the column most relevant to the user's search. It indicates whether the plan's financial activity should be posted to the General Ledger. It sits alongside related FRFS columns (FRFS_DISTR_MTHD_CD, FRFS_CNTR_DET_CD, FRFS_DISTR_DET_CD, FRFS_VAL_DET_CD, FRFS_PORTION_DET_CD), which together define flexible rule-based funding and distribution behavior.
  • PRMRY_FNDG_MTHD_CD — the primary funding method for the plan.
  • VSTG_APLS_FLAG, WVBL_FLAG — vesting applicability and waivability controls.
  • PER_CVRD_CD, PRORT_PRTL_YR_CVG_RSTRN_CD, PRORT_PRTL_YR_CVG_RSTRN_RL — who is covered and how proration for partial-year coverage is restricted.
  • NIP_* columns — non-imputed-plan/UOM and activity reference period attributes governing enrollment information timing.
  • DPNT_* columns — dependent-related controls such as date-of-birth and legal-ID requirements.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns used for incremental extraction and reconciliation.

Common Use Cases and Queries

The most frequent application is GL and funding reconciliation. A typical query lists plans whose activity posts to the ledger, joining to FRFS distribution configuration:

  • SELECT pl_id, name, pl_cd, post_to_gl_flag, prmry_fndg_mthd_cd FROM ben_pl_d WHERE post_to_gl_flag = 'Y'
  • SELECT pl_id, effective_start_date, effective_end_date, frfs_distr_mthd_cd, frfs_cntr_det_cd FROM ben_pl_d WHERE post_to_gl_flag = 'Y' AND sysdate BETWEEN effective_start_date AND effective_end_date
  • SELECT pl_id, name, short_code, last_update_date, last_updated_by FROM ben_pl_d WHERE last_update_date >= :last_run_date

Because the view is not distinct — multiple effective-dated rows exist per plan — every query should constrain on the effective dates unless a full history is deliberately required. The view is also commonly used in Fast Formula context variables and in custom extract programs feeding payroll-to-GL interfaces, where POST_TO_GL_FLAG acts as the primary gating condition.