Search Results ben_ptd_lmt_f_pk

Overview

BEN_PTD_LMT_F is a date-tracked (denormalized "F" table) entity within the Oracle E-Business Suite Advanced Benefits (BEN) module. It stores the definition of period-to-date (PTD) limits — thresholds that cap the cumulative amount contributed, distributed, or accrued for a compensation object, plan, or benefit program across a defined period such as a plan year, quarter, or month. These limits underpin statutory and plan-level controls, including annual contribution ceilings, catch-up restrictions, and employer match caps.

Each row is a dated definition: the combination of a PTD limit identifier with an effective start and end date describes a valid version of the limit rule over time. The presence of the OBJECT_VERSION_NUMBER column indicates Oracle's standard optimistic locking strategy, allowing the concurrent benefits engine and user interfaces to detect stale updates during enrollment and payroll processing.

Applying a heuristic Data Vault classification to the documented foreign-key structure, this table leans toward a satellite modeling suggestion: it carries descriptive, versioned attributes (name, calculation rule, balance type, attributes) keyed to a durable identifier (PTD_LMT_ID), rather than serving as a pure hub or connecting two distinct business entities. The single outbound reference to BEN_COMP_LVL_FCTR reinforces this descriptive, attribute-centric role.

Key Information Stored

The 49 documented columns fall into three functional groups: identity and versioning, limit semantics, and a wide descriptive attribute block.

• PTD_LMT_ID — the core business identifier for a period-to-date limit definition. It is the leading column of the composite primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-range pair completing the primary key, defining the validity window of each versioned definition.
• NAME — the user-facing label for the limit, used in enrollment screens and legislative reporting.
• BUSINESS_GROUP_ID — the operating unit or business group context that scopes the limit.
• LMT_DET_CD — the limit detail code distinguishing the specific limit category or rule type.
• COMP_LVL_FCTR_ID — the foreign key to BEN_COMP_LVL_FCTR; ties the limit to a compensation level factor used for eligibility or calculation.
• BALANCE_TYPE_ID — links the limit to a defined balance type, so contributions and distributions can be measured and netted correctly.
• PTD_LMT_CALC_RL — the calculation rule governing how the period-to-date value is derived and evaluated.
• MX_COMP_TO_CNSDR / MX_VAL / MX_PCT_VAL — the maximum components considered, absolute maximum value, and percentage-based maximum value supporting both flat and percentage limits.
• PDL_ATTRIBUTE_CATEGORY and PDL_ATTRIBUTE1–30 — the standard flexfield descriptive attribute block, used for client-specific extensions without schema change.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard Oracle WHO columns for audit and optimistic locking.

The unique index BEN_PTD_LMT_F_PK (PTD_LMT_ID, EFFECTIVE_END_DATE, EFFECTIVE_START_DATE) is the documented business-key candidate; it is identical to the primary key, so no separate surrogate key exists.

Common Use Cases and Queries

Functional consultants and developers query BEN_PTD_LMT_F to audit limit setup, trace which limits applied to an enrollment, or reconcile payroll deductions against statutory caps.

A typical as-of-date lookup returns the currently effective definition:

• SELECT ptd_lmt_id, name, lmt_det_cd, mx_val, mx_pct_val, ptd_lmt_calc_rl FROM ben_ptd_lmt_f WHERE ptd_lmt_id = :p_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Joining to BEN_COMP_LVL_FCTR ties each limit to its compensation factor:

• SELECT l.name, l.lmt_det_cd, f.* FROM ben_ptd_lmt_f l, ben_comp_lvl_fctr f WHERE l.comp_lvl_fctr_id = f.comp_lvl_fctr_id AND l.pdl_attribute_category = :cat;

Reporting use cases include reconciling year-to-date balances against MX_VAL and MX_PCT_VAL, identifying limits with client flexfield values via PDL_ATTRIBUTE1–30, and extracting dated history to examine how limits changed between plan years. Because the table is date-tracked, history-aware queries must always constrain on the effective date range to avoid returning multiple versions of the same limit.

Related Objects

• BEN_COMP_LVL_FCTR — referenced via BEN_PTD_LMT_F.COMP_LVL_FCTR_ID; the compensation-level factor that qualifies or scales the limit.
• BEN_PTD_LMT_F_PK — the unique index and primary key over (PTD_LMT_ID, EFFECTIVE_END_DATE, EFFECTIVE_START_DATE).
• BEN_BALANCE_TYPES (as referenced by BALANCE_TYPE_ID) — defines the balance that the period-to-date limit measures.
• BEN_PTD_LMT_BALANCES / derived limit tables — store computed period-to-date values that consume the definitions in this table, enabling actual-versus-limit reporting.
• BEN_ENRLD_PTIP_F and enrollment tables — carry the evaluated limit results applied to a specific person's enrollment.
• BEN_PL_F / BEN_PGM_F — plan and program definitions whose limit setup references this table.
• Oracle Advanced Benefits setup forms and the BEN setup validation APIs — manage creation and maintenance of these rows.
• CWB, PAY, and BEN reporting views — consume the effective-dated limit rows for legislative and total compensation reporting.